EXCELで、現在入力されている最終行がどこか知りたいという要望は、多いかと思います。
今回は、EXCEL VBA で入力セルの最終行の値を取得する処理を作成してみます。今回の内容は、要望の多さからEXCEL VBAでもかなりメジャーな手法かと思います。
また、汎用性が高いので、他の処理との相性も良いかと思います。
考え方
入力の最終行を取得する際のポイントは、Rangeオブジェクトの【Endプロパティ】と【Rowプロパティ】です。
Endプロパティとは
Endプロパティとは対象セル範囲の最終のRangeオブジェクトを返します。
コードでは以下の様に記述します。
Range(“検索起点のセル”).end(方向)
Excelの操作で「Ctrl」+ 矢印と同じ意味合いと思ってもらえるとイメージが付きやすいでしょうか。
(方向)の箇所は、以下の通りです。
- 上方向：xlUp
- 下方向：xlDown
- 右方向：xlToLeft
- 左方向：xlToRight
Rowプロパティとは
Rangeオブジェクトの行数を取得します。
上記のEndプロパティで取得したRangeオブジェクトからRowプロパティにて行数を取得します。
Range(“検索起点のセル”).end(方向).Row
例えば、A列の最終行の取得をするのであれば
Range(“A1”).end(xlDown).Row
といった形になります。
途中に空白がある場合の対応
前段の書き方でも最終行を取得することは可能ですが、途中に空白行があると正しい最終行が取得できないことを考慮しなくてなりません。
そのため、空白がある場合の対応策として、下から検索をかけます。
また、バージョンによってEXCELの最大行数が違う点も考慮し、Rangeオブジェクトの指定も書き方を変えます。(2007以降は,最大1048576行で2003までは、最大65536行です。)
以上を踏まえ、コードの記述は以下のようになります。
Cells(Rows.Count,対象列).End(xlUp).Row
Range(“対象セル”)でもCells(行,列)でもRangeオブジェクトは取得できます。
後々、使っていく時にどちらで指定するかは好みかなと思います。
サンプル
サンプルを作ってみました。
以下がサンプルのコードです。
Rangeの指定で下方向に検索した場合も記述してます。
Rangeの指定の処理では空白行までの行数を取得します。もちろん、制御を工夫すればRange指定でも空白がある場合の対応は可能です。
'果物の列の最終行を取得する処理
Private Sub CommandButton1_Click()
MsgBox ("果物は" & Cells(Rows.Count, 1).End(xlUp).Row & "行です。")
End Sub
'野菜の列の最終行を取得する処理
Private Sub CommandButton2_Click()
MsgBox ("野菜は" & Cells(Rows.Count, 2).End(xlUp).Row & "行です。")
'Range指定で空白行までを取得するコード
MsgBox ("野菜は" & Range("B1").End(xlDown).Row & "行です。")
End Subサンプル 共通関数にして作ってみる
前段にてコードの考え方と記述については完了しています。
ですが、この処理は汎用性が高いので、前段までの考え方を基に共通関数を作成してみます。
標準モジュールにPublicで関数を作ります。今回は、引数にシート名と列数を入れて作ってみます。
以下は標準モジュールの記述例です。
'対象シートの任意の列の最終行を取得する関数
Public Function GetLastRow(ByVal SheetName As String, ByVal ColumnCnt As Long) As Long
GetLastRow = Worksheets(SheetName).Cells(Rows.Count, ColumnCnt).End(xlUp).Row
End Function前段のサンプルで関数を呼び出す場合の記述例です。
Private Sub CommandButton1_Click()
MsgBox ("果物は" & GetLastRow("sheet1", 1) & "行です。")
End Sub
Private Sub CommandButton2_Click()
MsgBox ("野菜は" & GetLastRow("sheet1", 2) & "行です。")
End Subいずれも行数自体に大きな変化はありませんが、複数のシートの複数の列の入力最終行を取得したい場合などは作ってみてもよいかとは思います。
また、以前作成したプログレスバーの最大値として使用したりもできますのでお試しください。