Excel VBA 最後の行または列までの値をFor文で取得する方法 End(Direction)編

初めに

ExcelでVBAを書く際に、指定したセルからその最後の行または列までの値をFor文で取得する方法について記載します。

その前にセルを手操作で最終行を指定する方法について

飛ばしたい人は やり方へを飛んでください。
以下のようなデータがあるときに任意の場所からその最後の行または列を指定する場合は以下の方法で行えます。

操作方法

ctrl + 上矢印キー  # 指定したセルの列より下にある連続したデータの最後の行を取得する。
ctrl + 下矢印キー  # 指定したセルの列より上にある連続したデータの最初の行を取得する。
ctrl + 左矢印キー  # 指定したセルの行より左にある連続したデータの最初の列を取得する。
ctrl + 右矢印キー  # 指定したセルの行より右にある連続したデータの最後の列を取得する。

例)
「A1」セルを選択し「Ctrl + 下矢印キー」を押下した場合は以下のようになります。

【押下前】

【押下後】

「A10」セルを選択し「Ctrl + 上矢印キー」を押下した場合は以下のようになります。
【押下前】

【押下後】

「A1」セルを選択し「Ctrl + 右矢印キー」を押下した場合は以下のようになります。
【押下前】

【押下後】

「E1」セルを選択し「Ctrl + 左矢印キー」を押下した場合は以下のようになります。
【押下前】

【押下後】

このようにして手操作の場合は指定できます。

※注意
データ内に空白がある場合は空白の1つ前にあるデータへ飛ぶので注意してください。

【押下前】

【押下後】

この操作を基本にVBAのコードを考えていきます。

やり方

VBAで上記と同じ操作をする場合は以下のコードを書きます。

書き方

End(Direction)

「Direction」は方向を表し、それぞれ以下の値を実際には入れてください。(#以降は不要)

指定方法

xlUp      # 上
xlDown    # 下
xlToLeft  # 左
xlToRight # 右

実際には以下のように書きます。

書き方

End(xlUp)      ' ctrl + 上矢印キー
End(xlDown)    ' ctrl + 下矢印キー
End(xlToLeft)  ' ctrl + 左矢印キー
End(xlToRight) ' ctrl + 右矢印キー

実践

では実際の値を使用して操作してみましょう。

以下のようにシート名「実行」にある「セル取得」ボタンを実行するとシート名「ターゲット」のA列の「A1~A5」の値を取得するVBAを書くことを想定します。

【シート名「実行」】

【シート名「ターゲット」】

コード

Sub GetAnoterSheetValue_Btn()
    For i = 1 To Worksheets("ターゲット").Cells(Rows.Count, 1).End(xlUp).Row
        Debug.Print (Worksheets("ターゲット").Cells(i, 1).Value)
    Next
End Sub

上記の実行結果です。

実行結果

A1
A2
A3
A4
A5

上記ののコードの簡単な説明をします。
iの値を1からTOの後の値まで1ずつ増やしていきます。

For文

For i = 1 To
`任意の処理
Next

対象のシート名を指定しています。

シート名を指定

Worksheets("ターゲット")

「Rows.Count」はエクセルの「最大行数(1048576)」を表しますので以下の場合は「A列の1048576行目」を表します。

1列目の1048576行目のセルを指定

Cells(Rows.Count, 1)

「ctrl + 上矢印キー」を表します

ctrl + 上矢印キー

End(xlUp).Row

「ターゲット」シートの「i行目の1列目」のセルの値をコンソール(イミディエイト)に表示します。

セルの値をコンソールに表示

Debug.Print (Worksheets("ターゲット").Cells(i, 1).Value)

つまり以下のコードは、「1から＜ターゲットシートの1列目の最後の行のセルからctrl + 上矢印キーを実施した結果＞の行数までの1列の目のセルの値をコンソールに表示する」となります。

コード

For i = 1 To Worksheets("ターゲット").Cells(Rows.Count, 1).End(xlUp).Row
    Debug.Print (Worksheets("ターゲット").Cells(i, 1).Value)
Next

なので列番号を変更すると以下のように行数の違う2列目の値をちゃんと取得してくれます。

Sub GetAnoterSheetValue_Btn()
    For i = 1 To Worksheets("ターゲット").Cells(Rows.Count, 2).End(xlUp).Row
        Debug.Print (Worksheets("ターゲット").Cells(i, 2).Value)
    Next
End Sub

上記の結果です。

上記の結果

B1
B2
B3
B4
B5
B6

上記を参考に「A1~A5」を「xlDown」を使用して表示する場合は以下のようになります。

xlDownを使用した場合

Sub GetAnoterSheetValue_Btn()
    For i = 1 To Worksheets("ターゲット").Cells(1, 1).End(xlDown).Row
        Debug.Print (Worksheets("ターゲット").Cells(i, 1).Value)
    Next
End Sub

上記の実行結果です。

実行結果

A1
A2
A3
A4
A5

今度行ではなく「A1~E1」列の値を「xlToLeft」を使用して表示する場合は以下のようになります。

xlToLeftを使用した場合

Sub GetAnoterSheetValue_Btn()
    For i = 1 To Worksheets("ターゲット").Cells(1, Columns.Count).End(xlToLeft).Column
        Debug.Print (Worksheets("ターゲット").Cells(1, i).Value)
    Next
End Sub

上記の実行結果です。

実行結果

A1
B1
C1
D1
E1

※注意
「RowやRows」が「ColumnsやColumn」に変わっています。

上記を参考に「A1~E1」列の値を「xlToRight」を使用して表示する場合は以下のようになります。

xlToRightを使用した場合

Sub GetAnoterSheetValue_Btn()
    For i = 1 To Worksheets("ターゲット").Cells(1, 1).End(xlToRight).Column
        Debug.Print (Worksheets("ターゲット").Cells(1, i).Value)
    Next
End Sub

上記の実行結果です。

実行結果

A1
B1
C1
D1
E1

以上です。

最後に

ここまで見ていただきありがとうございます。