【ExcelVBA】データの最終行を取得する方法

と思ったこともあるのではないでしょうか。

そこで今回は、任意のシートと列を指定して最終行を取得する方法について紹介します。

コピペもできるので、使ってみてください。

VBAで最終行を取得するサンプルコード

Dim trgtSh As Worksheet
Set trgtSh = ThisWorkbook.Worksheets("サンプル")
Dim trgtLastRow As Long
trgtLastRow = trgtSh.Cells(trgtSh.Rows.Count, 1).End(xlUp).Row

MsgBox "「" & trgtSh.Name & "」シート「1」列目の最終行は「" & trgtLastRow & "」行目です。"

上記サンプルコードを実行すると「サンプル」シートが画像のデータを保持している時、最終行「10」行目を取得、次のMsgBoxを表示します。

次に詳しい解説をしていきます。

VBAで最終行を取得するコードの解説

Dim trgtSh As Worksheet
Set trgtSh = ThisWorkbook.Worksheets("サンプル")

この部分で、最終行を取得する対象シート「サンプル」シートを変数「trgtSh」に定義しています。

Dim trgtLastRow As Long
trgtLastRow = trgtSh.Cells(trgtSh.Rows.Count, 1).End(xlUp).Row

つづいて、最終行を変数「trgtLastRow」に定義しています。

この部分では「trgtSh.Cells(trgtSh.Rows.Count, 1).End(xlUp)」というRangeオブジェクトから「Row」プロパティで対象セルの行数を取得しています。

ここでポイントとなるのは次の2点です。
まず「trgtSh.Rows.Count」についてですが、これは「Rows.Count」によって「trgtSh」の行数をカウントした数を返します。つまり「trgtSh」に全部で何行あるかを数値で返すので「.xlsm」ファイルの場合、画像のように「1,048,576」を返します。

次に「End」プロパティについてですが、これは対象のセルから Ctrl を押しながら引数に指定した「方向」の 矢印キー を押した結果のセルを返します。

サンプルコードでは引数に「xlUp」つまり「↑」方向を指定していますので対象のセルから Ctrl + ↑ を押した結果を返します。また、この時の対象セルは「trgtSh.Rows.Count」で求めた行数と「1」列目を指定しているので「trgtSh.Cells(1048576, 1)」セルとなります。

よって「trgtSh」の「1」列目のデータがある最終セルは、Rangeオブジェクト「trgtSh.Cells(trgtSh.Rows.Count, 1).End(xlUp)」で取得することができます。そして、このRangeオブジェクトの「Row」プロパティから行数を取得して変数「trgtLastRow」に定義しています。

MsgBox "「" & trgtSh.Name & "」シート「1」列目の最終行は「" & trgtLastRow & "」行目です。"

最後に、取得した行数をMsgBoxで表示します。

また、今回は最終行取得の解説ですが、最終列の取得については下記を読んでください。

Endプロパティの引数

サンプルコードで使用した「End」プロパティについて、引数の一覧とそのイメージを紹介します。

VBAで最終行を取得する際の注意点と応用

データが一つもない時

今回紹介した最終行取得方法には注意点もあります。

それは「データが一つもない時」と「データが一つだけある時」の返り値が同じになってしまうことです。

データが一つでもある時と、一つもない時で処理を分けたい場合は、次のように最終行が「1」だった場合に1行目のセルが空白かどうかを判定して、処理を分岐するようにしましょう。

Dim trgtSh As Worksheet
Set trgtSh = ThisWorkbook.Worksheets("サンプル")
'最終行を取得
Dim trgtLastRow As Long
trgtLastRow = trgtSh.Cells(trgtSh.Rows.Count, 1).End(xlUp).Row

'最終行が「1」だった場合、開始セルにデータがあるかないかを判定
If trgtLastRow = 1 Then
    If trgtSh.Cells(trgtLastRow, 1).Value = "" Then
        
        'データが一つもない時の処理
        
    Else
    
        'データが一つだけある時の処理
    
    End If
End If

最終行取得処理を自作関数化する

最終行を取得する方法を紹介しましたが、最終行を取得したい場面が複数回に渡る場合、いちいち処理を書くのは手間がかかる上、可読性が低く、メンテナンスも大変です。

そこで、繰り返し使う処理を関数化して、書きやすく、可読性が高いコードに作りかえましょう。

今回の最終行を取得する処理の場合、必要なのは「対象シート」と「対象列」です。これを応用して、次のように「Worksheetオブジェクト」と「整数値」を渡して、最終行を返す関数を作ることができます。

'----------------------------------------------------------
'LastRow
'引数　　      ws, trgtCol
'返り値　      long型整数
'呼び出し元  -
'任意のワークシート、列数より、最終行を取得
'----------------------------------------------------------
Function LastRow(ByVal ws As Worksheet, ByVal trgtCol As Long) As Long
    Dim n As Long
    n = ws.Cells(ws.Rows.Count, trgtCol).End(xlUp).Row
    LastRow = n
End Function

上記の自作「LastRow」関数を使うと、最終行の取得を次のようにわかりやすく、すっきりとしたコードで書くことができます。

Dim trgtSh As Worksheet
Set trgtSh = ThisWorkbook.Worksheets("サンプル")
Dim trgtLastRow As Long
trgtLastRow = LastRow(trgtSh, 1)

また、今回は最終行取得関数を紹介しましたが、下記では最終列取得関数を紹介しているので、読んでみてください。
こうしたよく使う自作関数をまとめて一つのモジュールにしておくと、コピペで自作関数を全て実装することができるので作業効率が上がります。ぜひお気に入りの関数モジュールを作成して、使い回してみてください。

VBAで最終行を取得するまとめ

今回はVBAで任意のシート、列の最終行を取得する方法とそのポイントを紹介しました。
最終行の取得方法だけでなく、データが一つもない時の注意や、関数化して可読性を高める応用的な使い方についても理解しておくと、無駄がなく、汎用性の高いコードを書くことができるので、あわせて覚えておきましょう。