シートの最終列番号取得（.UsedRange）

Question

シートに記載された最終列の番号を取得したく、以下のコードを作成しました。

Dim r As Range
Dim last As Range

Set r = Worksheets("城").UsedRange.Select
last = Range("r").Column

ステップインで確認すると1行目は機能していて、2行目は「オブジェクトが必要です」と表示されます。
rは範囲（Range）なので機能すると思ったのですが・・・

改善方法、アドバイスいただければありがたいです。
よろしくお願いします。

Answer 1

    Dim r As Range
'    Dim last As Range
'    列番号は数値なので Range型ではなく、Long型
    Dim last As Long

'    Set r = Worksheets("城").UsedRange.Select
'    .Select が不要、Select はメソッド（選択するという動作）であってオブジェクトではない
     Set r = Worksheets("城").UsedRange

'  last = Range("r").Column
' Range("r") などという指定はできない
    last = r.Columns(r.Columns.Count).Column

最終列の番号取得には他にもいろいろな方法があります。
また、注意点もあります。下記に詳しく解説されています。

最終行・最終列の取得方法（End,CurrentRegion,SpecialCells,UsedRange）｜VBA技術解説

上記でも解説されてますが、UsedRange だと書式を設定（罫線とか背景色等）すると、そこも範囲になります。
データが入力されている領域の最終列ということなら、CurrentRegion を使った方かいいでしょう。

Comments

[ice930]

回答ありがとうございます。
Set r = Worksheets("城").UsedRange
last = Range("r").Column
のバージョンも試したのですが、「Rangeメソッドが失敗しました」となってしまいます。
原因に心当たり有りませんでしょうか。。。

[ice930]

とても丁寧な回答ありがとうございます！
理解しながらコードを記入できました！
ご教授いただいたサイトも参考にさせていただきます。
ありがとうございました！

Answer 2

下記コードで正しく動きます。

Dim r As Range
Dim last As Range

Set r = Worksheets("城").UsedRange
last = r.Columns(r.Columns.Count)

質問者様のコードがエラーになる原因

１．Set r = Worksheets("城").UsedRange.Select
まず、これは.Selectの戻り値がRangeオブジェクトではないためエラーが発生します。

２．last = Range("r").Column
まず、Range("r")と()内に"r"を入れていますが、これはアドレスとして不正な文字列なのでエラーになります。
おそらく、変数rのセル範囲を参照したかったのだと思いますが、使い方を勘違いされているようです。
そして、.Columnの戻り値はLong型であり、列番号を返すメソッドであるため、Range型の変数に代入できません。

Comments

[hatena19]

Dim last As Range は Dim last As Long ですよね。
「Range型の変数に代入できません。」といわれてますので編集忘れですね。

[ice930]

今後は戻り値やオブジェクトの種類に注意しながらコードを打とうと思います。
分かりやすい解説助かります！
ありがとうございました！

[ELBE]

.Column　の戻り値はLong型ですが、
.Columns　の戻り値はRange型です。

ただ、どのみちコードに誤りがありますね。
誤）last = r.Columns(r.Columns.Count)
正）Set last = r.Columns(r.Columns.Count)

[hatena19]

あ、そうですね。

質問では最終列の番号の取得ということなので、
Dim last As Long
last = r.Columns(r.Columns.Count).Column
の方が適していますね。

Answer 3

少し情報が足りない気がします。
シートに記載された最終列の番号とありますが、

Worksheets("城").UsedRange.Select

というコードの場合、例えばデータの始まりがB2セルで始まった場合、
1行目とA列は範囲選択から無視されます。その上で、指定された範囲内の列番号が必要なのか、
それともA1から数えた列番号が欲しいのか…その辺りが曖昧です。

また、必ずA1からデータがスタートし、空欄などもなくデータが並んでいるのであれば、
質問者さんの方法でなくとも

Dim l as Variant
Range("A1").End(xlToRight).Select
l = Selection.Column

などで一番右端の列が取得できるのでこのように列番号を拾ってやればいい気もします。
なんとなくですが、質問者さんのやりたいことはこのサイトに記載されている内容で解決しませんか？

https://www.tipsfound.com/vba/09002

Comments

[ice930]

回答ありがとうございます。
空欄の多い少し変わった表でしたので、今回は「UsedRange」を使用しておりました。
とても分かりやすいサイトを紹介いただきありがとうございます！

Answer 4

アクティブシートの最終セルの列番号の例です。

ActiveSheet.UsedRange.Column + ActiveSheet.UsedRange.Columns.Count - 1

Comments

[ice930]

このコードは思いつきませんでした！
次回参考にさせていただくかもしれません。
回答ありがとうございます！