【VBA】最終行を取得する３つの方法と特徴の比較

最終行を取得する方法はたくさんのページで紹介されています。

しかし、記事によって内容が違うため、少し混乱したことはないでしょうか。

最終行の取得方法はいくつかあり、それぞれ特徴があるため、目的に応じて使い分ける必要があります。

ちなみに、よくわからないときは「とりあえずこれを使っておけば大丈夫」という方法も存在します。

今回はVBAで最終行を取得する３つの方法と、おまけに関数を応用した方法について解説します。

それぞれの方法を知っておくことで、目的やシーンに合ったコードを書くことができるようになりますので、ぜひチェックしてみてください。

シンプル版（下方向検索）

Sub 最終行取得_xlDown()
'列を下方向に検索（ただし、数式入力のない空白セルがあれば止まる）

    Dim Er As Long
    Er = Cells(1, "A").End(xlDown).Row
    
    MsgBox Er

End Sub

「Cells(1, “A”).End(xlDown).Row」は、「A1」セルを起点に、A列を下方向に検索し、最初の空白セルに当たるまでの行番号を返します。

なお、サンプル表に記載している赤字の「数式あり」「数式なし」などはないものとして見てください。

この場合、A列の結果は「10」となりますが、空白セルが途中にあるB列を指定した場合の結果は「4」となります。

また、数式が15行目まであるC列を指定した場合、数式の入力もカウントされてしまうので、結果は「15」となります。

数式に反応してしまう上に、空白セルが途中にあるとそこを最終行と認識していまうので、使えるシーンは限定されます。

シンプル版（上方向検索）

Sub 最終行取得_xlup()
'列を上方向に検索（ただし、数式による空白セルを無視できない）

    Dim Er As Long
    Er = Cells(Rows.Count, "A").End(xlUp).Row
    
    MsgBox Er

End Sub

「Cells(Rows.Count, “A”).End(xlUp).Row」はA列を上方向に検索し、何らかの入力に当たった時点の行番号を返します。

なお、こちらも数式の結果が空白のセルを「入力あり」とみなします。

つまり、数式入力のないA列やB列の結果は「10」となりますが、数式が15行目まであるC列を指定した場合、数式に反応してしまうので、結果は「15」となります。

数式のある場所では使えないと考えるのが無難です。（数式の入力されている最終行を取得したい場合は使えます。）

応用版（数式空欄セルを無視）

Sub 最終行取得_ForNext()
'列の表示値の最終行を取得

    Dim Er As Long
    For Er = Cells(Rows.Count, "A").End(xlUp).Row To 1 Step -1
        If Cells(Er, "A") <> "" Then Exit For
    Next Er
    
    MsgBox Er

End Sub

先ほどの２つのコードと比べると少し長くなってしまいますが、上方向検索の応用で、A、B、C列のいずれも結果は「10」となります。

検索方法は「シンプル版（上方向検索）」と同じで、指定列を下から上方向に検索します。

違うのは、「If Cells(Er, “A”) <> “” Then Exit For」の部分で、検索した値を1セルずつチェックしていることです。

この場合、結果が表示上の空白でなくなった場所で検索を終了し、その行番号が最終行として返されます。

これなら検索列の途中にある空白セルに引っかかることもなく、数式による表示が空白のセルも無視して、目で見える何らかの表示がされている最終行を正確に取得できます。

ただし、何らかの表示があるセルの下側に、大量の数式による空欄がある場合、これまでに紹介した２つの方法と比べると処理時間が微増してしまうので注意してください。

適切な使い分けができるに越したことはありませんが、よくわからないうちはこちらを使うのも一手です。

おまけ（カウントの応用）

最終行の取得ではないですが、最終行の取得の代わり、または補助的に使える方法をいくつかご紹介します。（比較的コードが短く使いやすいシート関数を紹介します。）

Counta関数

Sub Counta1()
'範囲内の入力セル数をカウント（数式による空欄を含む）

    Dim Cnt As Long
    Cnt = WorksheetFunction.CountA(Range("A1:A100"))

    MsgBox Cnt

End Sub

「A1:A100」の入力セルをカウントするので結果は「10」になります。

数式による空白セルもカウントするため、例えば「C1:C100」を指定した場合は「15」となります。

また、「B1:B100」の場合は途中に空白セルがあるので「8」となります。

つまり、最終行を求めるために使えるのは、次の条件をすべて満たす場合だけです。

このような条件が整ったときのみ、

「入力セルの合計＝最終行」

という読み替えができます。

厳しい条件のように感じますが、意外と使えるシーンは多いかも。

それに、VBAコードに不慣れな方にとって、初歩的なシート関数と同じ感覚で目的が果たせるのは一定の意味があるように思います。

Countif関数

Countifは条件に一致する値をカウントするシート関数です。

Sub Countif()
'範囲内の数値（数式による空白セルは含まない）

    Dim Cnt As Long
    Cnt = WorksheetFunction.CountIf(Range("A1:A100"), ">=" & 0)

    MsgBox Cnt

End Sub

指定範囲の数値表示セルのみカウントするので結果は「9」になります。

このコードは、カウント対象を数値に限定することで、表示されている数値のみをカウントし、数式による空白セルを無視することができます。

例えば「C1:C100」を指定した場合は「7」となります。

最終行を求めるために使える条件は次のとおりです。

この条件で初めて、

「入力セルの合計＋ヘッダー行数＝最終行」

という読み替えができます。

少しややこしいですね。

使用例として、次の表を見てください。

日付欄には、「№の入力がある場合のみ日付を表示する」という数式がB2からB15まで入力されています。

Excelで日付はシリアル値と呼ばれる数値扱いです。（文字列設定がされている場合を除く。）

ここで次のコードを実行するとどうなるでしょうか。

Sub Countif()

    Dim Cnt As Long
    Cnt = WorksheetFunction.CountIf(Range("B2:B15"), ">=" & 0)

    MsgBox Cnt

End Sub

結果は「8」です。

数式による空白セルを無視してくれています。

Excelで日付はシリアル値と呼ばれる数値扱い（文字列設定がされている場合を除く。）なので、「”>=” & 0」という条件で数値以外を無視できるわけです。

一応、試しにCuntaを使って同じ範囲をカウントした場合を見てみましょう。

Sub Counta1()

    Dim Cnt As Long
    Cnt = WorksheetFunction.CountA(Range("B2:B15"))

    MsgBox Cnt

End Sub

結果は「14」となります。

数式による空白セルもカウントしてしまうためです。

Contif関数のカウント対象を絞れる特性を使い、数式による空白セルを無視する方法の紹介でした。

このCuntif関数を使って出した「8」に、タイトル行の「1」を足して「9」というように、最終行を求めることができます。

まとめ

以上、最終行を取得する基本的な方法をいくつか紹介しました。

どれを使うべきかわからない状況ではとりあえずこのページの「実用版（おすすめ）」で紹介したものを使っておけば困ることはないと思います。

状況によっては処理時間が微増する可能性がありますが、１処理でわかるほどの差はないはずです。

ご参考いただけると幸いです。