【Excel VBA】リストの最終行、列を取得する

エクセルで作成したリストへのマクロ処理を実行する場合、新規データ追加等でリストの最終行、列が変化していくことがあるので、マクロ実行時にリストの最終行や列を取得することで、自動的にマクロ処理の範囲を変更できるようにします。

VBAでは、RangeオブジェクトのEndプロパティを利用することで、最終行、列を「基準とするセル」と「指定した方向」から「終端セル」をRangeオブジェクトとして取得することができます。
なお、空白を基準とした場合は指定した方向に対して次のデータが入力されているセルが終端セル扱いとなります。

下図のサンプルリストの①～④について、「基準とするセル」と「指定した方向（VBA定数）」から、マクロ実行時に取得される「終端セル」がどうなるかを示します。

	基準とするセル	指定した方向	VBA定数	終端セル
①	C6	下	xlDown	C8
②	C1048576	上	xlUp	C11
③	A7	右	xlToRight	C7
④	XFD6(16384列目)	左	xlToLeft	G6

実際に①～④の条件で終端セルを取得するコードは下記の通りです。

Sub sample()

Dim ①, ②, ③, ④ As String

'①基準とするセル＝C6、指定した方向＝下(xlDown)
① = Range("C6").End(xlDown).Address(False, False)

'②基準とするセル＝C1048576、指定した方向＝上(xlUp)
② = Range("C1048576").End(xlUp).Address(False, False)

'③基準とするセル＝A7、指定した方向＝右(xlToRight)
③ = Range("A7").End(xlToRight).Address(False, False)

'④基準とするセル＝XFD6、指定した方向＝左(xlToLeft)
④ = Range("XFD6").End(xlToLeft).Address(False, False)

MsgBox "①の場合の終端セル：" & ① & vbCrLf & _
       "②の場合の終端セル：" & ② & vbCrLf & _
       "③の場合の終端セル：" & ③ & vbCrLf & _
       "④の場合の終端セル：" & ④

End Sub

実行結果は下のメッセージボックスの通りです。

①（リストの1行目～）と②（エクセルの最終行～）の終端セルが異なるのは、リストに空白行が存在するために発生しているので、リストの最終行を取得する場合は、空白行があるかもしれないことを前提として、最終行(1048576行目)もしくは一連No等を使用して空白行のない列を用意する等の対応が望ましいです。