【Excel VBA】最終行の取得方法

ExcelのVBAでは、表の全行に対して処理を行いたい場合など、データが入力されている行の最終行を取得する必要が、よく発生します。
以下のような表で、最終行「11」を取得する方法を、4通り説明いたします。

Excelの最終行の取得方法　その①

End(xlUp) を使用

Sheets("Sheet1").Range("A" & Rows.Count).End(xlUp).Row
または
Sheets("Sheet1").Cells(Rows.Count, 1).End(xlUp).Row

解説

一体何をしているのか、順に説明します。

Range または Cells の部分

Excelのセルの位置を表します。よくわからない方は、以下の記事をご覧ください。

【Excel VBA】RangeとCellsを使ったセルの参照方法

ExcelのVBAにて欠かせない、RangeとCellsを使ったセルの参照方法をご説明します。

hakase-studio.com

2025.02.26

また、セルを扱う場合は、上記のSheets(“Sheet1”)のように、シート名を必ず指定するクセをつけて下さい。
ブック内に複数のシートが存在する場合に、正しく動かなくなります。

Rows.Count　の部分

これは、Excelシートの最大行を表します。
つまり、Excel2007以降（拡張子が.xlsx）の場合は「1048576」、Excel97-2003形式（拡張子が.xls）の場合は「65536」です。

End(xlUp)　の部分

これは、キーボードの [CTRL]キー + [↑]キーを押した状態を表します。
Endの部分が[CTRL]キー、(xlUp)の部分が[↑]キーに対応しています。
つまり、セル[A1048576]（Excel97-2003形式の場合はセル[A65536]）を選択した状態で、[CTRL]キー + [↑]キーを押すということです。

すると、何が起きるかと言うと、

セル[A11]にジャンプしました。
これはExcelの機能ですが、[Ctrl]キーを押しながら矢印（↑↓←→のいずれか）のキーを押すことで、データが入力されているセルまでジャンプできます。

Row　の部分

対象のセルの行番号を取得します。 上記の例ですと、セル[A11]の行番号、つまり「11」を求めることができます。

例

今回はあくまで、「最終行を取得する」だけのサンプルとして、行番号をメッセージボックスに表示してみます。

Sub text()
    MsgBox Sheets("Sheet1").Cells(Rows.Count, 1).End(xlUp).Row
End Sub

無事、「11」が表示されました。

注意点①：最終行が非表示になっている場合

この方法で、最終行を正しく取得できない場合があります。
それは、オートフィルタなどで最終行が非表示になっている場合です。
このような場合は、「表示されているセルにおける最終行」が、実行結果となります。

例えば、上記の表でこのような状態になっている場合

データは11行目まで入力されていますが、オートフィルタにより10行目と11行目が非表示になっているため、先ほどの

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

の実行結果は、「11」ではなく「9」になります。

「11」を求めたい場合は、オートフィルタの解除などで、全ての行を表示してから、上記の方法を使うか、下記の方法③または方法④をご使用ください。

なお、オートフィルタの解除は、以下の1文を実行すれば出来ます。

Sheets("Sheet1").ShowAllData

注意点②：不規則な形の表の場合

下表のように、不規則な形の表（未入力の行がある場合など）の場合にも、最終行を正しく取得できません。

この表で

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

を実行すると、結果は「10」になります。
例えば「A列はキー項目なので必ず入力されている」という保証があれば問題ありません。
しかし、そうでない場合は、以下の方法②以降を使う必要があります。

補足

今回、Excelの最大行から[CTRL]キー + [↑]キーを押す方法を使いました。
しかし、1行目（セル[A1]）で[CTRL]キー + [↓]キーを押しても、良さそうに見えると思います。
つまり、以下の方法です。

Sheets("Sheet1").Range("A1").End(xlDown).Row

この方法では、下表のように途中に空白セルが存在した場合に、空白セルの手前（下図の例だとセル[A5]）が最終行になってしまいます。
そのため、最大行から[CTRL]キー + [↑]キーを押す方法が確実です。

Excelの最終行の取得方法　その②

SpecialCells(xlCellTypeLastCell) を使用

Sheets("Sheet1").Range("A1").SpecialCells(xlCellTypeLastCell).Row

この方法では、Range(“A1”)を起点とした最終セルを返します。
SpecialCells(xlCellTypeLastCell)で、[CTRL]キー + [End]キー を押すのと同じ結果が得られます。
以下のシートで上記コードを実行すると、「11」が返されます。

Excelの最終行の取得方法　その③

UsedRange を使用

Sheets("Sheet1").UsedRange.Rows(Sheets("Sheet1").UsedRange.Rows.Count).Row

長いので、Withでまとめると

With Sheets("Sheet1").UsedRange
    .Rows(.Rows.Count).Row
End With

この方法では、シートで使われているセル範囲を返します。
コードが長くて複雑ですが、オートフィルタなどで非表示になっている行があっても、非表示の行を含めた最終行を返すというメリットがあります。

但し、方法②と同様に、セルの書式設定（罫線やセルの色など）がされている箇所まで範囲に入るので、ご注意ください。

Excelの最終行の取得方法　その④

CurrentRegion を使用

Range("A1").CurrentRegion(Sheets("Sheet1").Range("A1").CurrentRegion.Count).Row

この方法では、Range(“A1”)を起点とした隣接セル範囲を返します。
[CTRL]キー + [End]キー を押すのと同じ結果が得られます。

セルの書式設定（罫線やセルの色など）は考慮せず、値が入力されているセル範囲のみを対象とします。
方法③と同様に、オートフィルタなどで非表示になっている行があっても、非表示の行を含めた最終行を返すというメリットがあります。

但し、途中に空行があると、空行の前までしか範囲に入らないので、ご注意ください。
例えば以下のような表では、空行の前までの「5」が最終行として返されます。

まとめ

以上、最終行を求める方法を4通り、ご紹介しました。
改めてまとめると、以下の通りです。
いずれの方法も一長一短があるので、場合に応じて使い分けてください。