【Excel VBA】最終行を取得する方法5選！取得できない対処法も！

VBAで最終行を取得したいときはないでしょうか。

けど、そんな中で悩むことは、

ですよね。

今回はそんなお悩みを解決する

についてまとめます！

VBAで最終行を取得するするイメージ

VBAで最終行を取得するイメージについて説明をします。

Excel側に表を作成します。

VBAを実行すると、Excel表の最終行を取得することができます。

それを５パターン、それぞれの方法やうまくいかない場合の対処法について説明をします。

実行結果

それでは早速試してみましょう。

タカヒロ

それぞれの方法には条件によって最終行判定の結果が異なりますので、後半に検証をおこない、結果の違いについて説明をします。

VBAで最終行を取得する方法①：一番下のセルへ移動しxlUpで上の最後セルを選択する。

VBAで最終行を取得する方法①一番下のセルへ移動しxlUpで上の最後セルを選択する方法について説明をします。

最終行を取得する表を準備する

Excel側に最終行を取得する表を作成します。
最終行を取得する対象の列はA列となります。

VBAコード

サンプルのVBAコードは以下の通りです。

Sub 最終行を取得する_方法①()

    Debug.Print Cells(Rows.Count, 1).End(xlUp).Row

End Sub

VBAを設定する

Cellsで１列目を指定しています。２列目以降で最終行を判定したい場合はこの数字を変更してください。

VBAの実装

VBAの実装方法については
VBAの実装手順
をご参照ください。

VBAを実行する

VBAを実行してみましょう。

イミディエイトウィンドウに最終行の結果が表示されましたね！

VBAの説明

VBAについて説明をします。

Rows.Countで列の最終行数を算出します。xls形式では65536、xlsx形式では1048576個となります。
次にCtrl + ↑のキー操作をEnd(xlUp)で実現し、Rowプロパティで行数を取得します。

タカヒロ

VBAで最終行を取得するときはこの方法が多いですね。

なお、この①の方法は指定した列のみの最終行判定となるため、
以下のように他の列に値が追加されたとしても最終行判定の対象にはなりません。

VBAで最終行を取得する方法②：Ctrl+ENDキー操作をSpecialCells(xlLastCell)で再現する

VBAで最終行を取得する方法②：Ctrl + ENDキー操作をSpecialCells(xlLastCell)で再現する方法について説明をします。

VBAコード

サンプルのVBAコードは以下の通りです。

Sub 最終行を取得する_方法②()

    Debug.Print Cells(1, 1).SpecialCells(xlLastCell).Row
    
End Sub

VBAを実行する

先ほどの表を対象にし、

VBAを実行してみましょう。

最終行が取得できましたね！

B列９行目に値を追加してみましょう。

A列以外の列も最終行が取得できましたね！

VBAの説明

VBAについて説明をします。

Cells(1, 1)でA1セルを指定し、次にCtrl + ENDキー操作でA1以降に続く表の最終行が取得できますが、それと同じ操作をSpecialCells(xlLastCell)で実現していきます。
その行位置をRowプロパティで取得します。

今回はA列以外の列に値が追加されても最終行を取得することが可能です。

VBAで最終行を取得する方法③：一番下のセルへ移動しCtrl+ENDキー操作で取得する

VBAで最終行を取得する方法③：一番下のセルへ移動しCtrl + ENDキー操作で取得する方法について説明をします。

VBAコード

サンプルのVBAコードは以下の通りです。

Sub 最終行を取得する_方法③()

    Debug.Print Cells(Rows.Count, 1).SpecialCells(xlLastCell).Row
    
End Sub

VBAを実行する

VBAを実行してみましょう。

最終行が取得できましたね！

B列９行目に値を追加してみましょう。

A列以外の列も最終行が取得できましたね！

VBAの説明

方法①と同様にRows.Countで列の最終行数を算出します。
次に方法②と同様にCtrl + ↑のキー操作をEnd(xlUp)で実現し、Rowプロパティで行数を取得します。
最後にCtrl + ENDキー操作と同じ操作をSpecialCells(xlLastCell)で実現していきます。
その行位置をRowプロパティで取得します。

VBAで最終行を取得する方法④：UsedRangeで表の使用範囲から取得する

VBAで最終行を取得する方法④：UsedRangeで表の使用範囲から取得する方法について説明をします。

VBAコード

サンプルのVBAコードは以下の通りです。

Sub 最終行を取得する_方法④()

    With Worksheets(1).UsedRange
        Debug.Print .Rows(.Rows.Count).Row
    End With

End Sub

VBAを実行する

VBAを実行してみましょう。

最終行が取得できましたね！

B列９行目に値を追加してみましょう。

A列以外の列も最終行が取得できましたね！

VBAの説明

１シート名の表使用範囲をUsedRangeで取得します。

Selectメソッドで実行すると以下のように使用範囲が選択されます。

Rows.Countで使用表の列の最終行数を算出します。
次に行位置をRowプロパティで取得します。

VBAで最終行を取得する方法⑤：UsedRangeで表の使用範囲と絶対位置で取得する

VBAで最終行を取得する方法⑤：UsedRangeで表の使用範囲と絶対位置で取得する方法について説明をします。

VBAコード

サンプルのVBAコードは以下の通りです。

Sub 最終行を取得する_方法⑤()
    
    With Worksheets(1).UsedRange
        Debug.Print .Cells(1.1).Row + .Rows.Count - 1
    End With

End Sub

VBAを実行する

VBAを実行してみましょう。

最終行が取得できましたね！

B列９行目に値を追加してみましょう。

A列以外の列も最終行が取得できましたね！

VBAの説明

１シート名の表使用範囲をUsedRangeで取得します。

Cells(1.1).Rowで使用表の先頭行の位置番号を求めます。

次にRows.Countで使用表の最終行数を求め、合算します。なお、１多くなる結果となるので、1を減算します。

VBAで最終行がうまく取得できない場合の原因と対処法

なにかしらの書式が適用されている

原因

値が空であっても罫線など書式が適用されていると、最終行として判定される場合があります。
サンプルでは罫線の書式をA9セルに設定しています。

各パターンの結果は以下の通りです。

VBAコード

Sub 最終行を取得するALLパターン()
               
    Debug.Print "方法その①" & Cells(Rows.Count, 1).End(xlUp).Row
    Debug.Print "方法その②" & Cells(1, 1).SpecialCells(xlLastCell).Row
    Debug.Print "方法その③" & Cells(Rows.Count, 1).SpecialCells(xlLastCell).Row
    
    With Worksheets(1).UsedRange
        Debug.Print "方法その④" & .Rows(.Rows.Count).Row
        Debug.Print "方法その⑤" & .Cells(1.1).Row + .Rows.Count - 1
    End With

End Sub

実行結果

対処

空のセルなど最終行判定から除外したいセルの書式を削除しましょう。

タカヒロ

書式が多様されている場合は方法①を採用したほうがよいでしょう。

計算式が挿入されている

原因

見た目は空であっても計算式が挿入されていると、最終行として判定される場合があります。
サンプルではB9セルとC9セルの値を文字結合する数式していますが、
両セルとも空のため表示されるものはありません。

各パターンの結果は以下の通りです。

実行結果

対処

計算式を削除するか、計算結果で値がなければ処理を除外するようにしましょう。

タカヒロ

弊ブログでもこの原因によるエラーの問い合わせは多いです。例えばVLOOKUP関数で条件に該当しない場合、セルを空表示している場合です。見た目だけでは判断できないので、確認が難しく、なかなか厄介ですね。

値を削除後すぐに処理をする

原因

値を削除後すぐに処理をすると、最終行として判定される場合があります。
９行目に適当な値をいれて削除し、セルが選択されている状態で処理をしてみます。

各パターンの結果は以下の通りです。

実行結果

対処

値削除後にカーソルを動かすなどして表示画面をリフレッシュし、キャッシュデータが残らないようにしましょう。

タカヒロ

最終行判定をSpecialCells(xlLastCell)の方法で行う場合は、更新が多い表を対象にするとこの事象に出くわす可能性があるので、別の方法にしておいたほうがよいでしょう。

半角/全角スペース文字が挿入されている

原因

半角/全角スペース文字が挿入されていると、最終行として判定されます。

各パターンの結果は以下の通りです。

実行結果

対処

半角/全角スペース文字を削除しましょう。

タカヒロ

その他、Alt + Enterの改行コードのみが入力されている場合も発生しますので注意してください。

VBAの実装手順

実装手順は以下の通りです。

Excel側にVBAを実装していきます。

①Excelを新規に開き、「開発」タブをクリックし、「VisualBasic」をクリックします。
もしくはショートカットキー「Alt」＋「F11」でもOKです。

②標準モジュールを追加します。
左ペインのVBAProjectを右クリックし、「挿入」、「標準モジュール」を選択します。

③右ペインのウインドウに上記のVBAを入力します。

こちらで完了です。

VBAを実行する

では早速VBAの実行をしてみましょう。

①「開発」タブの「VBA」をクリックし実行したいマクロを選択し、「実行」をクリックします。

②処理がされたことが確認できれば完了です。

さいごに

いかがでしょうか。

今回は、

についてまとめました。

また、他にも便利な方法がありますので、よろしければご参照頂ければと思います。