Excel VBA で最終行を求める — データの本当の終端を見つける（各手法に潜む罠）

TL;DR — Range("A1:A100") のベタ書きはやめましょう。データは増えたり減ったりするので、その端は実行時に求めます。定番イディオムは Cells(Rows.Count, "A").End(xlUp).Row — 列の一番下から Ctrl+↑ を押す動作を再現します。Rows.Count を使い、65536 のベタ書きは絶対にしないこと。「データはどこで終わるのか？」を問う方法は 4 つあり、それぞれ少しずつ違う問いに答えます。.End(xlUp) は 1 つの列の中で空白でない最後のセルを、.End(xlDown) は最初の切れ目で止まり、.UsedRange は過大に報告し、.Find だけが本当に空白に強い手法です。手法を間違えると、間違った行を処理してしまいます — 警告してくれるエラーは何も出ません。

最終行を求めることは、マクロがループ・コピー・並べ替え・消去を行う前に、最もよく必要とする処理です。そして、初心者が手続き全体で最も壊れやすい 1 行を書いてしまう場所でもあります — A2:A500 のベタ書きです。行を足せばマクロはそれを見落とし、行を消せば数百もの空白を処理します。本物の VBA は、データのサイズを決して仮定しません — 測るのです。

この記事で学べること

定番イディオム Cells(Rows.Count, "A").End(xlUp).Row を 1 つずつ分解して理解する
65536 がなぜ地雷で、Rows.Count がなぜ正解なのか
4 つの手法（.End(xlUp)・.End(xlDown)・.UsedRange・.Find）それぞれに潜む失敗パターン
最終行だけでなく、最終列を求める方法
データに空白があるとき、なぜ .End から .Find に切り替えなければならないのか

考え方の軸：データには「端」があり、Excel はそれを教えてくれない

ワークシートは 100 万行を超えるグリッドです。データはその中のどこかの長方形を占めていますが、Excel には「最終行」をそのまま読める気の利いたプロパティは用意されていません。ですから、自分で端まで歩いて、どこに着いたかを確かめる必要があります。信頼できる手法はどれも、「データより先にあると分かっている場所からスタートし、実際に中身のある最後のセルまで飛び戻る」ことの変奏にすぎません。

定番イディオムのイメージは、列の一番下に立って Ctrl+↑ を押す人です。カーソルが一気に上へ飛び、中身のある最後のセルで止まります。.End(xlUp) がコードの中でやっているのは、まさにこれです。

Sub FindLastRow()
    Dim ws As Worksheet
    Set ws = ActiveSheet

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

    Debug.Print "Last row with data in column A: " & lastRow
End Sub

内側から読み解きましょう。ws.Rows.Count はシートの行数（現代の Excel では 1,048,576）です。Cells(その値, "A") は A 列の一番下のセル。.End(xlUp) は空白でない最後のセルまで上に飛びます。.Row はその行番号を読み取ります。この 1 行が主力です — ただし、それが抱える罠を理解している場合に限ります。

最悪の最終行バグを防ぐルール：65536 をベタ書きしない

十年来、チュートリアルは Cells(65536, 1).End(xlUp).Row と書いてきました。65,536 は古い .xls 形式の最終行だったからです。現代の .xlsx シートには 1,048,576 行あります。もしデータが 65,536 行目より下まで続いているのに、65,536 行目からジャンプを始めると、データの一部より上からスタートすることになり、小さすぎる最終行を無言で返します — ループが表の途中で打ち切られてしまうのです。

' 壊れやすい — 65536 行目より下にデータがあると、その上からスタートしてしまう
lastRow = Cells(65536, 1).End(xlUp).Row

' 正しい — 常にこのシートの本当の最下端からスタートする
lastRow = Cells(Rows.Count, 1).End(xlUp).Row

Rows.Count は、コードが動くシートの本当の行数を読み取るので、将来のバージョンにもフォーマットにも強くなります。数値をベタ書きした方が良い場面は存在しません。 Cells(Rows.Count, col) を反射的に書けるようにしましょう。

各手法を失敗パターンに結びつけるルール

どのチュートリアルも飛ばすのがここです。よく使われる 4 つの手法は、同じ答えを返しません。それぞれ違う問いに答えているからです。各手法の失敗パターンを覚えれば、どれに手を伸ばすべきか常に分かります。

.End(xlUp) — 1 つの列の中で空白でない最後のセル。 既定の手法です。盲点は、 1 つの列しか見ないこと。最後の行が空になっている列（たとえば 500 行目の名前は空だが金額は入っている）を選ぶと、手前で止まってしまいます。すべてのデータ行で必ず埋まっている列 — ID やキー — を選ぶか、.Find で表全体にわたって最終行を求めましょう。

.End(xlDown) — 上から見て最初の切れ目。 これは 1 行目からの Ctrl+↓ です。最終行は見つけません。見つけるのは、最初の空白セルの 1 つ手前の行です。列の途中にたった 1 つ空セルがあるだけで、先頭のブロックまでで切り詰められてしまいます。データの終端を求めるのに .End(xlDown) を使ってはいけません — 途切れのない連続範囲の最下端を求めるときだけにしましょう。

' A1:A10 にデータがあり、A6 は空白、A7:A10 にもデータがある
Cells(1, 1).End(xlDown).Row     ' -> 5   (切れ目で止まる — 「最終行」としては誤り)
Cells(Rows.Count, 1).End(xlUp).Row  ' -> 10  (本当の最終行 — 正しい)

.UsedRange — Excel がキャッシュした外接矩形。 ws.UsedRange はあなたのデータではありません。Excel がこれまでに触れたすべてのセル — 書式だけが設定されたセルも含みます — であり、行を削除しても、ブックを保存または再計算するまで 縮みません。過大に報告するので、UsedRange をループすると数百もの幽霊行を延々と処理しかねません。また、必ずしも 1 行目から始まるとは限りません（FAQ 参照）。 UsedRange は「ざっくりシート全体」と捉え、正確な最終行の番号としては決して使わないでください。

.Find — 唯一、空白に強い手法。 シート上のどこであれ何かが入っている最後のセルを後ろ向きに検索する方法は、途中の空白にも、どの列を選ぶかにも左右されません。

Function LastDataRow(ws As Worksheet) As Long
    Dim c As Range
    Set c = ws.Cells.Find(What:="*", After:=ws.Cells(1, 1), _
                          LookIn:=xlFormulas, SearchOrder:=xlByRows, _
                          SearchDirection:=xlPrevious)
    If c Is Nothing Then
        LastDataRow = 0            ' 空のシート
    Else
        LastDataRow = c.Row
    End If
End Function

.Find は記述が長く、Nothing の場合（空のシート）を処理する必要がありますが、データの右端が不揃いだったり途中に空白があったりするときは、これが正解です。 LookIn:=xlFormulas は、今は "" に評価される数式でも、数式が入っているセルを見つけます。xlValues はそれを無視します — 意図して選びましょう。

判断の目安：いつ、どの手法を使うか

きれいな 1 列のリスト、または確実に埋まったキー列を持つ表 → Cells(Rows.Count, keyCol).End(xlUp).Row。速く、読みやすく、正確です。
途中に空白行がある、または右端が不揃いなデータ → .Find(What:="*", SearchDirection:=xlPrevious)。空白に強い手法です。
「シート上のもの全体がだいたいどれくらいの大きさか、消去したいので知りたい」 → .UsedRange。消すのが目的なら、過大報告は無害です。
最終行を求めるのに .End(xlDown) を使ってはいけません。また、 UsedRange.Rows.Count を最終行の番号のように読んでもいけません。

最終列も求める

同じジャンプの技は横方向にも効きます。見出し行の一番右のセルからスタートし、コードで Ctrl+← を押します。

Dim lastCol As Long
lastCol = Cells(1, Columns.Count).End(xlToLeft).Column

この 2 つの数値があれば、何もベタ書きせずに正確なデータ範囲を組み立てられます。

Dim rng As Range
Set rng = ws.Range(ws.Cells(1, 1), ws.Cells(lastRow, lastCol))

これこそが肝心な点です。端を見つけられるようになれば、固定アドレスを二度と打ち込まなくて済みます。見出し行を飛ばした範囲を作るには、これを Offset と Resize と組み合わせます。連続ブロックを 1 回の呼び出しで取得するには、UsedRange と CurrentRegion を参照してください。

ExcelMaster の活用

「最終行を求めたい」という質問のほとんどは、実はもっと大きな目標への一歩です。この列をループして重複に印を付ける、新しい行を別シートにコピーする、グループごとに合計する、といった具合に。ExcelMaster は、その目標を平易な日本語で受け取ります — 「注文テーブルの各行について、未完了のものを Backlog シートにコピーして」といった具合に — そして範囲の計算を代わりに書き、固定サイズを前提にするのではなく、確実な方法でデータの端を見つけます。

定期実行するマクロの中では、これからも .End(xlUp) を手で書くことになるでしょう。しかし「今日そこにある行を何でも処理する」という日々の作業なら、Rows.Count や空白処理を毎回正しく書くより、結果を言葉で説明する方が勝ります。

よくある質問

VBA で最終行を求める最良の方法は？

確実に埋まったキー列を持つ表なら、Cells(Rows.Count, keyColumn).End(xlUp).Row を使います。シートの下端からの Ctrl+↑ を再現するもので、速く読みやすい方法です。データの途中に空白行があったり右端が不揃いだったりする場合は、代わりに Cells.Find(What:="*", SearchDirection:=xlPrevious).Row を使いましょう。.End は切れ目で止まることがあるからです。

なぜ 65536 ではなく Rows.Count を使うべきなのですか？

65536 は古い .xls 形式の最終行でした。現代の .xlsx シートには 1,048,576 行あるので、65,536 行目から上向きにジャンプを始めると、それより下にあるデータの上からスタートし、小さすぎる最終行を返してしまいます。Rows.Count は常に、現在のシートの本当のサイズを反映します。

なぜ UsedRange は誤った最終行を返すのですか？

UsedRange は、Excel がこれまでに触れたすべてのセル（書式だけのセルも含む）をキャッシュした外接矩形で、データを削除してもファイルを保存するまで縮みません。しかも 1 行目から始まるとは限らないので、UsedRange.Rows.Count は行数であって、最終行の番号ではありません。正確な最終行には .End(xlUp) か .Find を使いましょう。

VBA で最終列を求めるには？

最終行のイディオムを横向きにするだけです。Cells(1, Columns.Count).End(xlToLeft).Column と書きます。見出し行の一番右のセルから、中身のある最後の列まで戻るようにジャンプします。

列に空白があるとき、最終行を求めるには？

.End(xlUp) は 1 つの列しか調べないので、その列の最後のデータ行が空白だと、手前で止まってしまいます。常に埋まっている列（ID など）に向けるか、 Cells.Find(What:="*", SearchDirection:=xlPrevious).Row を使いましょう。これは空白に関係なく、シート上のどこであれ中身のある最後のセルを見つけます。

検証環境

検証環境: Excel 365（Windows 11）、VBA 7.1 — 最終確認 2026-07-30。

関連ガイド： VBA Offset · VBA UsedRange vs CurrentRegion · VBA Range · VBA For Loop