Excel VBAでマクロを作成する際、データが入力されている「最終行」や「最終列」を取得する処理は、最も基本でありながら最も重要なテクニックです。繰り返し処理（For文）の終了条件を指定したり、新しいデータを一番下に追加したりするためには、現在のデータがどこまで入っているかを正確に把握する必要があります。
しかし、VBAを学習して間もない方や、実務で複雑なデータを扱っている方の多くが、「マクロを実行したら最終行が途中で止まってしまった」「データがないはずのずっと下の行番号が取得されてしまう」「非表示行があると正しい行が取れない」といったトラブルに直面します。
実は、VBAで最終行・最終列を取得する方法には「Endプロパティ」「UsedRange」「CurrentRegion」「Findメソッド」など様々なアプローチが存在し、それぞれに「得意な状況」と「致命的な弱点」があります。これらを理解せずに使ってしまうことが、取得ズレの最大の原因です。
本記事では、最終行・最終列が正しく取得できない原因を徹底的に解明し、データに空白が含まれている場合や、数式で見えない空文字が入っている場合など、あらゆる状況に対応できる「正しい最終行・最終列の取得方法」をVBAコード付きで体系的に解説します。この記事を読めば、データの追加やループ処理でエラーを出すことは二度となくなります。
目次
- 1. なぜ「最終行・最終列」の取得でズレやエラーが起きるのか？
- 2. 【王道】End(xlUp) / End(xlToLeft) を使った最も安全な取得方法
- 3. 王道のEndプロパティでも最終行がズレる3つの原因と対策
- 4. UsedRangeの罠：実際の最終行より下を取得してしまう理由
- 5. CurrentRegionプロパティを使った取得方法と注意点
- 6. 【最強】Findメソッドを使った「確実な」最終行・最終列の取得方法
- 7. テーブル（ListObject）機能を使っている場合の取得方法
- 8. 実務で使える！状況別のベストプラクティス（まとめ）
- 9. よくある質問（FAQ）
1. なぜ「最終行・最終列」の取得でズレやエラーが起きるのか？
VBAで最終行や最終列を取得する際、意図した結果にならない原因は、Excelのシート上で「見た目のデータ」と「システムが認識しているデータ」に乖離があるためです。
人間が目で見て「ここが最終行だ」と思う場所と、VBAが計算して導き出す場所が異なるのは、以下のような「見えない要素」が存在するからです。
- データとデータの間に「空白セル」が挟まっている
- セルの中身は空に見えるが、数式によって
""（空文字）が返されている - 文字は入力されていないが、背景色や罫線などの「書式」だけが設定されている
- オートフィルタや非表示設定によって、行が隠れている
これらの要因を無視して、ただネットで拾った最終行取得のコードをコピペしていると、予期せぬ場所を最終行と誤認し、データを上書きして破壊してしまったり、不要な空白行まで延々とループ処理を続けてマクロがフリーズしたりする原因となります。
2. 【王道】End(xlUp) / End(xlToLeft) を使った最も安全な取得方法
実務において、90%以上の場面で推奨される最もスタンダードで安全な最終行・最終列の取得方法が、End プロパティを使用した方法です。
これは、Excelのシート上で対象のセルを選択し、キーボードの「Ctrlキー + 矢印キー」を押したときの動きをVBAで再現したものです。
正しい最終行の取得：下から上へ探す（xlUp）
最終行を取得する際、「上から下へ（xlDown）」探すのはNGです。途中に空白セルがあった場合、そこで止まってしまうからです。正しい方法は、シートの一番下の行（1048576行目）から、上に向かって（xlUp）データが入っているセルを探すというアプローチです。
Sub GetLastRow()
Dim lastRow As Long
' A列（1列目）のシート最下端から上に向かって最初のデータを探す
lastRow = Cells(Rows.Count, 1).End(xlUp).Row
MsgBox "A列の最終行は " & lastRow & " 行目です。"
End Sub
Rows.Count は、そのExcelバージョンにおける最大行数（現在のExcelなら1048576）を自動で返します。これにより、どのバージョンのExcelでも確実に最下端から探索を開始できます。
正しい最終列の取得：右から左へ探す（xlToLeft）
最終列を取得する場合も考え方は同じです。シートの一番右の列（XFD列など）から、左に向かって（xlToLeft）探します。
Sub GetLastColumn()
Dim lastCol As Long
' 1行目のシート最右端から左に向かって最初のデータを探す
lastCol = Cells(1, Columns.Count).End(xlToLeft).Column
MsgBox "1行目の最終列は " & lastCol & " 列目です。"
End Sub
3. 王道のEndプロパティでも最終行がズレる3つの原因と対策
前述の End(xlUp) は非常に強力ですが、完璧ではありません。特定の条件下では、正しい最終行を取得できなくなります。
原因1：指定した列にデータがない（基準列の選択ミス）
Cells(Rows.Count, 1) は「A列」を基準に最終行を探します。もし、B列やC列には100行目までデータがあるのに、A列には50行目までしかデータがない場合、最終行は「50」と判定されてしまいます。
【対策】 必ず「データが一番下まで隙間なく、あるいは確実に最も下まで入力されている列」を基準列として指定してください。列番号を 1 ではなく 2 や “C” などに変更します。
原因2：非表示行（オートフィルタ等）が存在する
オートフィルタ機能で行を絞り込んでいる時や、手動で行を非表示にしている時に End(xlUp) を実行すると、「表示されているセルの中での最終行」を取得してしまいます。非表示になっている本当の最終行は見逃されます。
【対策】 マクロの処理の先頭で、一時的にオートフィルタを解除（またはすべてのデータを表示）してから最終行を取得する処理を挟む必要があります。
' オートフィルタがかかっていれば全表示にする
If ActiveSheet.FilterMode Then
ActiveSheet.ShowAllData
End If
' この後にEnd(xlUp)を実行する
原因3：数式で「””（空文字）」が返されている
IF関数などで =IF(A1="", "", A1*10) のように、条件を満たさない場合に「””（空文字）」を表示させているセルがある場合、見た目は空白でも、VBAの End(xlUp) は「数式というデータが入っている」と判定し、空文字のセルを最終行として取得してしまいます。
【対策】 数式による空文字を無視して、本当に「値」が表示されている最終行を取得したい場合は、後述する Findメソッド を使用する必要があります。
4. UsedRangeの罠：実際の最終行より下を取得してしまう理由
列を指定せずに、シート全体のどこかにデータがある最後の行を取得したい場合に、ActiveSheet.UsedRange を使うコードがよく紹介されます。しかし、これは実務において非常に危険でバグを生みやすい罠です。
【よくある間違った書き方】
' 罠1：開始行が1行目以外だとズレる
lastRow = ActiveSheet.UsedRange.Rows.Count
' 罠2：値がなくても書式があるとそこを最終行とする
lastRow = ActiveSheet.UsedRange.Row + ActiveSheet.UsedRange.Rows.Count - 1
UsedRangeがずれる原因
UsedRange は、直訳すると「使用されたセル範囲」です。VBAにとっての「使用された」とは、文字が入力されたことだけを指しません。「背景色が塗られている」「罫線が引かれている」「一度文字を入力してDeleteキーで消した（メモリ上に使用履歴が残っている）」だけでも、UsedRangeに含まれてしまいます。
そのため、データは10行目までしかないのに、100行目まで背景色が設定されていると、UsedRangeは100行目を最終行として返してしまいます。
UsedRangeの使用履歴をリセットする方法
もしどうしてもUsedRangeを使いたい場合は、取得する直前に一度UsedRangeを呼び出して（再計算させて）、使用履歴のキャッシュをリセットするおまじないを入れる必要がありますが、書式設定による拡張はリセットできません。基本的には最終行の取得にUsedRangeを使用するのは避けるべきです。
5. CurrentRegionプロパティを使った取得方法と注意点
CurrentRegion は、指定したセルを起点として、空白の行と列で囲まれた「表全体（ひとまとまりのデータ）」を取得するプロパティです。キーボードの「Ctrl + A」を押した時の挙動に似ています。
Sub GetLastRowWithCurrentRegion()
Dim lastRow As Long
Dim lastCol As Long
With Range("A1").CurrentRegion
' A1から連続する表の最終行と最終列を取得
lastRow = .Rows.Count + .Row - 1
lastCol = .Columns.Count + .Column - 1
End With
MsgBox "表の最終行: " & lastRow & " / 最終列: " & lastCol
End Sub
CurrentRegionの弱点
表の途中に「完全に空白の行」や「完全に空白の列」が1つでも存在すると、そこで表が途切れたとみなされてしまいます。
そのため、ユーザーが手入力で作成した「空白行が混ざっているかもしれないリスト」に対して使用すると、実際のデータよりも手前の行を最終行と誤認するリスクがあります。システムからエクスポートされた、必ず隙間なくデータが詰まっているCSVファイルなどを扱う場合にのみ有効な手段です。
6. 【最強】Findメソッドを使った「確実な」最終行・最終列の取得方法
「どの列が一番下まで入っているか分からない」「途中に空白行がある」「数式による空文字（””）を無視したい」「非表示行も考慮したい」といった、あらゆる不安定な要素をすべて解決できる究極の取得方法が、Findメソッドを使用したアプローチです。
Findメソッドで「*（ワイルドカード：何らかの文字）」をシートの最後尾から逆順に検索し、一番最初に見つかったセルを最終行（最終列）とします。
数式の空文字(“”)を無視して、本当に「値」がある最終行を取得する
Function GetRealLastRow(ByVal ws As Worksheet) As Long
Dim foundCell As Range
' シート全体を対象に「値(xlValues)」として何らかの文字(*)が入っているセルを、
' 最後尾から上に向かって(xlPrevious)行単位(xlByRows)で検索する
Set foundCell = ws.Cells.Find(What:="*", _
After:=ws.Cells(1, 1), _
LookIn:=xlValues, _
LookAt:=xlPart, _
SearchOrder:=xlByRows, _
SearchDirection:=xlPrevious, _
MatchCase:=False)
If Not foundCell Is Nothing Then
GetRealLastRow = foundCell.Row
Else
' シートが完全に空の場合は1を返す
GetRealLastRow = 1
End If
End Function
' --- 呼び出し側の例 ---
Sub TestFindLastRow()
Dim lastRow As Long
lastRow = GetRealLastRow(ActiveSheet)
MsgBox "真の最終行は " & lastRow & " です。"
End Sub
真の最終列を取得する
列を取得する場合は、SearchOrder を xlByColumns（列単位で検索）に変更するだけです。
Function GetRealLastCol(ByVal ws As Worksheet) As Long
Dim foundCell As Range
Set foundCell = ws.Cells.Find(What:="*", _
After:=ws.Cells(1, 1), _
LookIn:=xlValues, _
LookAt:=xlPart, _
SearchOrder:=xlByColumns, _ ' ← ここを列単位に変更
SearchDirection:=xlPrevious, _
MatchCase:=False)
If Not foundCell Is Nothing Then
GetRealLastCol = foundCell.Column
Else
GetRealLastCol = 1
End If
End Function
Findメソッドの優位性：
引数 LookIn:=xlValues と指定することで、数式そのものではなく「数式の結果表示されている値」を検索対象にします。そのため、結果が ""（空文字）のセルは無視され、本当に文字や数値が表示されている最後の行を確実に見つけ出すことができます。また、列を指定しないため、A列が短くB列が長い場合でも、シート全体の中で最も下にあるデータの行番号を取得できます。
7. テーブル（ListObject）機能を使っている場合の取得方法
Excelの「テーブル機能（挿入＞テーブル）」を利用してリストを管理している場合、シートの最終行を探す必要はありません。VBAにはテーブルオブジェクト（ListObject）を直接操作するプロパティが用意されており、これを使うのが最もスマートです。
Sub GetTableLastRow()
Dim tbl As ListObject
Dim dataCount As Long
Dim tableLastRow As Long
' "テーブル1" という名前のテーブルを取得
Set tbl = ActiveSheet.ListObjects("テーブル1")
' テーブル内のデータの行数（見出し行を含まない）を取得
dataCount = tbl.ListRows.Count
MsgBox "テーブルには " & dataCount & " 件のデータがあります。"
' シート上の絶対的な行番号を取得したい場合
' テーブルのデータ範囲の最初の行番号 + データ件数 - 1
If dataCount > 0 Then
tableLastRow = tbl.DataBodyRange.Row + dataCount - 1
MsgBox "シート上でのテーブル最終行は " & tableLastRow & " 行目です。"
Else
MsgBox "テーブルにデータがありません。"
End If
End Sub
テーブルを使えば、途中に完全な空白行があっても、書式が乱れていても、テーブルの枠組みの中での確実な行数を取得できます。データの追加や削除が多いシステムでは、データをテーブル化してからVBAで処理する設計が非常に推奨されます。
8. 実務で使える！状況別のベストプラクティス（まとめ）
ここまで解説した様々な手法を、実際の業務シーンでどのように使い分けるべきかをまとめます。
- シーン1：1つの列に必ずデータが詰まっている（ID列や日付列がある）場合
迷わずCells(Rows.Count, "A").End(xlUp).Rowを使います。コードが短く、実行速度も最速です。 - シーン2：どの列が一番下までデータが入っているか予測できない場合
Findメソッドを使用して、シート全体から値がある最終行を探します。 - シーン3：数式が多用されており、IF関数で「””」が設定されている場合
End(xlUp)では空文字を拾ってしまうため、必ずFindメソッド (LookIn:=xlValues)を使います。 - シーン4：ユーザーが自由に色や罫線を設定するシートの場合
UsedRangeは書式設定に騙されて無関係な下の行を取得するため、絶対に使用してはいけません。 - シーン5：システムのCSVエクスポートなど、綺麗な四角形のデータの場合
Range("A1").CurrentRegionを使うと、一発で表全体の範囲が取得できて便利です。
9. よくある質問（FAQ）
Q1. End(xlUp)を使ったら、最終行が「1」になってしまいました。
指定した列に全くデータが入力されていない、あるいは1行目（見出し）しか入力されていない場合に発生します。Rows.Count（一番下）から上に向かって探した結果、1行目の見出しまでたどり着いてしまったためです。データを追加するマクロの場合は、If lastRow < 2 Then lastRow = 1 などの分岐を入れて、2行目から書き込みを始めるような処理を記述する必要があります。
Q2. 行が非表示の時にEnd(xlUp)を使うとどうなりますか？
End(xlUp)は「表示されているセル」のみを対象とします。例えば、100行目までデータがあり、50行目〜100行目を非表示にしている状態でEnd(xlUp)を実行すると、最終行は「49行目」と判定されます。正確な最終行を取得したい場合は、処理の前に非表示を解除するか、Findメソッドを使用してください（Findメソッドは非表示行も検索対象に含めることが可能です）。
Q3. SpecialCells(xlCellTypeLastCell) という書き方を見ましたが、使っても良いですか？
ActiveSheet.Cells.SpecialCells(xlCellTypeLastCell) は、キーボードの「Ctrl + End」を押した時の動作を再現します。しかし、これは内部的に UsedRange と全く同じ仕組み（使用履歴や書式設定に依存する）を利用しているため、実際のデータがない、ずっと下のセルを取得してしまうバグの温床になります。実務での使用は推奨されません。
まとめ
最終行・最終列の取得は、VBAプログラミングの土台となる処理です。この土台が不安定だと、どれだけ高度な処理を書いてもエラーが発生してしまいます。
基本は End(xlUp) を使いこなし、数式やレイアウトが複雑なシートでは Findメソッド を呼び出す、という2つの武器を状況に応じて使い分けることで、どのようなExcelファイルであっても堅牢でエラーの出ないマクロを構築することができます。ぜひ本記事のコードをご自身のプロジェクトに取り入れて、安定したマクロ開発を行ってください。