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

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

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

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

・VBAで最終行を取得したいが方法がよくわからない
・VBAで最終行を取得したが思うように取得できず困っている

ですよね。

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

・VBAで最終行を取得する方法5選
・VBAで最終行がうまく取得できない場合の原因と対処法

についてまとめます!

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

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

Excel側に表を作成します。
最終行を取得する表

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

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

実行結果

方法その①8
方法その②8
方法その③8
方法その④8
方法その⑤8

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

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

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

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

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

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

VBAコード

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

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

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

End Sub

VBAを設定する

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

Cells(Rows.Count, 1)

VBAの実装

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

VBAを実行する

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

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

8

VBAの説明

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

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

Cells(Rows.Count, 1).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を実行してみましょう。

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

8

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

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

9

VBAの説明

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

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

Cells(1, 1).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を実行してみましょう。
最終行判定の表

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

8

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

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

9

VBAの説明

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

Cells(Rows.Count, 1).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を実行してみましょう。
最終行判定の表

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

8

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

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

9

VBAの説明

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

With Worksheets(1).UsedRange

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

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

.Rows(.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を実行してみましょう。
最終行判定の表

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

8

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

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

9

VBAの説明

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

With Worksheets(1).UsedRange

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

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

Cells(1.1).Row + .Rows.Count - 1

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

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

原因

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

実行結果

方法その①8
方法その②9
方法その③9
方法その④9
方法その⑤9

対処

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

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

計算式が挿入されている

原因

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

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

実行結果

方法その①9
方法その②9
方法その③9
方法その④9
方法その⑤9

対処

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

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

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

原因

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

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

実行結果

方法その①8
方法その②9
方法その③9
方法その④8
方法その⑤8

対処

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

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

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

原因

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

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

実行結果

方法その①9
方法その②9
方法その③9
方法その④9
方法その⑤9

対処

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

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

VBAの実装手順

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

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

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

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

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

こちらで完了です。

VBAを実行する

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

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

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

さいごに

いかがでしょうか。

今回は、

・VBAで最終行を取得する方法5選
・VBAで最終行がうまく取得できない場合の原因と対処法

についてまとめました。

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



この記事の関連キーワード

こちらの記事の関連キーワード一覧です。クリックするとキーワードに関連する記事一覧が閲覧できます。








コメントを残す

メールアドレスが公開されることはありません。 * が付いている欄は必須項目です