【VBA】空白を含む最終行を正しく取得する方法｜End(xlUp)がズレる原因

VBAでデータ処理をするとき、最終行の取得はほぼ必ず使う処理です。

しかし、実務のExcelデータでは、途中に空白行があったり、一部の列だけ値が抜けていたり、数式だけが残っていたりすることがあります。

その状態で何となく End(xlUp) を使うと、想定より上の行を最終行として取得してしまうことがあります。

最終行がズレると、コピー範囲が足りない、集計対象が欠ける、削除処理が途中で止まるなど、後から気づきにくい不具合につながります。

この記事では、空白を含むデータで最終行を正しく取得する考え方と、End(xlUp) がズレる原因、実務で安全に使えるVBAコードを解説します。

✅ VBAで空白を含む最終行を取得するときの基本

VBAで最終行を取得する処理は、一見すると簡単に見えます。

しかし、実務データでは「どの列を基準にするか」によって取得結果が変わります。

A列には空白があるけれどB列には値がある、という表では、A列基準で最終行を取得すると正しい行まで処理できないことがあります。

そのままループやコピー処理に使うと、最終行より下のデータが無視される可能性があります。

まずは、最終行取得が「表全体の最終行」なのか「特定列の最終行」なのかを分けて考えることが大切です。

・最終行取得でよく使われる基本コード

最終行取得でよく使われるのが、次のコードです。

Sub GetLastRowByColumnA()

    Dim targetSheet As Worksheet
    Dim lastRow As Long

    Set targetSheet = ThisWorkbook.Worksheets("データ")

    ' A列を基準に、下から上へ向かって最後のデータ行を探す
    lastRow = targetSheet.Cells(targetSheet.Rows.Count, "A").End(xlUp).Row

    MsgBox "A列の最終行は " & lastRow & " 行目です。"

End Sub

この書き方は、A列に必ずデータが入っている表であれば扱いやすい方法です。

Rows.Count でシートの最終行からスタートし、End(xlUp) で上方向に移動して、最初に値があるセルの行番号を取得しています。

・この書き方が便利な場面

この方法は、次のような表で向いています。

A列に管理番号が必ず入っている
A列に日付が必ず入っている
データの途中に空白行がない
最終行の基準列が明確に決まっている

たとえば、A列に「注文番号」が必ず入る売上データであれば、A列基準で最終行を取得しても大きな問題は起きにくいです。

ただし、A列に空白が混ざる可能性があるなら、この書き方だけに依存するのは危険です。

End(xlUp) は最終行取得の定番ですが、列方向の最終列取得や xlToLeft の使い分けまで理解しておくと、可変データの処理をより安全に設計できます。Endメソッドの基本から実務での活用方法については、「【VBA】Endメソッドで最終行・最終列を取得する方法｜xlUp・xlToLeftの実務活用」も参考にしてみてください。

✅ End(xlUp)が空白行でズレる原因

End(xlUp) は便利ですが、「表全体の最後」を自動で判断してくれる機能ではありません。

指定した列の中で、下から上に向かって最初に見つかったデータ行を返すだけです。

そのため、基準列に空白があると、実際には下にデータが残っていても、上の行を最終行として取得してしまうことがあります。

この仕組みを誤解すると、VBAコード自体はエラーにならないのに、処理結果だけが間違うという厄介な状態になります。

実務では、エラーが出るマクロより、静かに間違った範囲を処理するマクロの方が危険です。

・A列に空白があると最終行が途中で止まる例

たとえば、次のような表があるとします。

行	A列：商品コード	B列：商品名	C列：数量
1	商品コード	商品名	数量
2	A001	ノート	10
3	A002	ペン	5
4		消しゴム	8
5	A004	定規	3
6		ファイル	12

この状態でA列を基準にすると、最終行は5行目として取得される可能性があります。

しかし、表としては6行目にもデータがあります。

つまり、A列だけを見ると正しく見えても、表全体としては最終行を取りこぼしている状態です。

・ズレを防ぐには基準列の選び方が重要

最終行を取得するときは、次のように考えると安全です。

必ず入力される列があるなら、その列を基準にする
どの列にも空白の可能性があるなら、表全体から探す
数式が入っている列は、結果が空白でも「データあり」と判定される場合がある
見た目の空白とVBA上の空白は違うことがある

特に実務では、「A列が必ず埋まるはず」という前提が崩れることがあります。

手入力データ、CSV取込データ、他部署から受け取ったファイルでは、基準列を過信しない方が安全です。

✅ 空白を含む表ではFindで最終行を取得する

表のどの列にデータが入るか分からない場合は、Find メソッドを使ってシート全体から最後のデータセルを探す方法が有効です。

End(xlUp) は指定列だけを見るのに対し、Find は範囲全体からデータを探せます。

そのため、途中に空白がある表や、列によって入力状況が異なる表でも、最終行を取得しやすくなります。

ただし、Find は引数を省略すると前回の検索条件の影響を受けることがあるため、実務では条件を明示して書くことが重要です。

ここを省略すると、別の操作後に急に結果が変わるような分かりにくい不具合につながることがあります。

・表全体から最終行を探すコード

次のコードは、シート全体から最後にデータが入っている行を取得します。

Sub GetLastRowByFind()

    Dim targetSheet As Worksheet
    Dim lastCell As Range
    Dim lastRow As Long

    Set targetSheet = ThisWorkbook.Worksheets("データ")

    ' シート全体から、最後にデータが存在するセルを探す
    Set lastCell = targetSheet.Cells.Find( _
        What:="*", _
        LookIn:=xlFormulas, _
        LookAt:=xlPart, _
        SearchOrder:=xlByRows, _
        SearchDirection:=xlPrevious, _
        MatchCase:=False)

    If lastCell Is Nothing Then
        MsgBox "データが入力されているセルはありません。"
        Exit Sub
    End If

    lastRow = lastCell.Row

    MsgBox "表全体の最終行は " & lastRow & " 行目です。"

End Sub

このコードでは、シート全体の中から最後にデータがあるセルを探し、その行番号を取得しています。

SearchOrder:=xlByRows と SearchDirection:=xlPrevious を組み合わせることで、行方向に見たときの最後のデータを探しています。

・このコードを実務向けにしている理由

このコードでは、Find の検索条件を省略せずに書いています。

理由は、Excelの検索設定が前回の操作や別の処理の影響を受けることがあるためです。

たとえば、検索対象が「値」なのか「数式」なのか、完全一致なのか部分一致なのかによって、結果が変わる可能性があります。

実務で使うコードでは、「今のExcelの状態に依存するコード」よりも、「コード内で条件が分かる書き方」の方が安全です。

また、データが存在しない場合に lastCell Is Nothing で処理を止めている点も重要です。

この確認を入れないと、空のシートでエラーが発生する可能性があります。

・LookInをxlFormulasにするかxlValuesにするか

Find で最終行を取得するときは、LookIn の指定が重要です。

LookIn:=xlFormulas

にすると、数式が入っているセルも対象になります。

一方で、

LookIn:=xlValues

にすると、表示されている値を基準に探します。

実務では、次のように使い分けると分かりやすいです。

指定	向いている場面
xlFormulas	数式セルもデータとして扱いたい
xlValues	表示結果があるセルだけを対象にしたい

たとえば、数式で "" を返しているセルを最終行として扱いたくない場合は、xlValues の方が意図に近いことがあります。

逆に、数式が入力されている行を処理対象に含めたい場合は、xlFormulas が向いています。

今回のように最終行取得で Find を利用する場合は、検索条件の指定方法や省略による落とし穴も理解しておくことが重要です。Findメソッドの設計思想や安全な使い方については、「【VBA】文字列のセル位置を検索する方法｜Findの正しい使い方と設計思想」で詳しく解説しています。

✅ 複数列を基準にして最終行を取得する方法

シート全体から探すほどではないけれど、A列だけでは不安という場合もあります。

たとえば、A列の商品コードは空白になることがあるが、B列の商品名かC列の数量には必ず何か入る、というケースです。

この場合は、複数列の最終行を取得し、その中で一番大きい行番号を採用する方法が使えます。

表の構成がある程度決まっている場合には、シート全体を対象にするより意図が明確になります。

「どの列までをデータ範囲とみなすのか」をコードに表現できる点も、保守性の面でメリットがあります。

・A列からC列の中で最大の最終行を取得するコード

Sub GetLastRowFromMultipleColumns()

    Dim targetSheet As Worksheet
    Dim checkColumns As Variant
    Dim columnName As Variant
    Dim currentLastRow As Long
    Dim maxLastRow As Long

    Set targetSheet = ThisWorkbook.Worksheets("データ")

    ' 最終行を確認したい列を配列で管理する
    checkColumns = Array("A", "B", "C")

    maxLastRow = 1

    For Each columnName In checkColumns

        currentLastRow = targetSheet.Cells(targetSheet.Rows.Count, CStr(columnName)).End(xlUp).Row

        If currentLastRow > maxLastRow Then
            maxLastRow = currentLastRow
        End If

    Next columnName

    MsgBox "A列からC列の中で最も下にあるデータ行は " & maxLastRow & " 行目です。"

End Sub

このコードでは、A列からC列までを確認し、その中で最も大きい行番号を最終行として採用しています。

A列が空白でも、B列やC列にデータがあれば、その行を最終行として扱えます。

・複数列方式が扱いやすい理由

この書き方のメリットは、確認する列を明示できることです。

シート全体を対象にすると、表の外にあるメモや注意書きまで最終行として拾ってしまうことがあります。

一方で、複数列方式なら「この表ではA列からC列をデータ範囲として扱う」とコード上で明確にできます。

後からD列も対象にしたくなった場合は、

checkColumns = Array("A", "B", "C", "D")

のように変更するだけで対応できます。

仕様変更にも強く、実務で管理しやすい書き方です。

・複数列方式で注意するポイント

この方法でも、対象列すべてが空白の場合は正しい判断ができません。

また、見出し行だけがあるシートでは、最終行が1行目として取得されます。

そのため、実際の処理に使う場合は、

データ開始行より下にデータがあるか
見出しだけの状態ではないか
対象列の範囲は適切か

を確認しておくと安全です。

✅ データ開始行を考慮して最終行を判定する

最終行を取得できても、データが存在するとは限りません。

たとえば、1行目に見出しだけがあり、2行目以降にデータがない場合でも、A列基準では最終行が1行目になります。

その状態で2行目から最終行までループしようとすると、意図しない処理になる可能性があります。

実務では、最終行を取得するだけでなく、「処理対象データが存在するか」まで確認することが重要です。

この一手間が、後から起きる原因不明の不具合をかなり減らしてくれます。

・見出しだけのシートを判定するコード

Sub CheckDataExistsBeforeProcessing()

    Dim targetSheet As Worksheet
    Dim lastRow As Long
    Dim firstDataRow As Long

    Set targetSheet = ThisWorkbook.Worksheets("データ")

    firstDataRow = 2

    ' A列を基準に最終行を取得する
    lastRow = targetSheet.Cells(targetSheet.Rows.Count, "A").End(xlUp).Row

    If lastRow < firstDataRow Then
        MsgBox "処理対象のデータがありません。"
        Exit Sub
    End If

    MsgBox "処理対象データは " & firstDataRow & " 行目から " & lastRow & " 行目までです。"

End Sub

このコードでは、データ開始行を firstDataRow として変数化しています。

見出しが1行目で、データが2行目から始まる前提であれば、lastRow < firstDataRow のときは処理対象なしと判断できます。

・データ開始行を変数にするメリット

実務では、表の見出しが1行とは限りません。

タイトル行、説明行、集計条件などが上部にある場合、データ開始行が5行目や10行目になることもあります。

そのため、コード内に直接 2 と書くより、

firstDataRow = 2

のように意味のある変数として管理した方が、後から変更しやすくなります。

あとでデータ開始行が3行目に変わっても、変数の値を変更するだけで済みます。

小さな工夫ですが、実務で長く使うマクロほど効いてくるポイントです。

最終行を取得できても、実際に処理対象のデータが存在するとは限りません。空白判定やデータ有無の確認を組み合わせた安全な処理については、「【VBA】範囲内に値があるか確認する方法｜空白判定と実務で安全なチェック処理」も参考になります。

✅ 実務では最終行取得を関数化すると使いやすい

最終行取得は、さまざまな処理で繰り返し使います。

コピー、転記、削除、集計、チェック処理など、ほとんどのVBA自動化で登場します。

毎回同じようなコードを書くと、修正漏れや書き方のばらつきが起きやすくなります。

そこで、最終行を取得する処理を関数化しておくと、複数のマクロで再利用しやすくなります。

特に、空白を含む表を扱う場合は、最初から安全な取得方法を部品化しておくと便利です。

・Findを使った最終行取得を関数化するコード

Function GetLastUsedRow(ByVal targetSheet As Worksheet) As Long

    Dim lastCell As Range

    ' シート全体から最後に使われているセルを探す
    Set lastCell = targetSheet.Cells.Find( _
        What:="*", _
        LookIn:=xlFormulas, _
        LookAt:=xlPart, _
        SearchOrder:=xlByRows, _
        SearchDirection:=xlPrevious, _
        MatchCase:=False)

    If lastCell Is Nothing Then
        GetLastUsedRow = 0
    Else
        GetLastUsedRow = lastCell.Row
    End If

End Function

この関数は、対象シートを渡すだけで、シート内の最終行を返します。

データがない場合は 0 を返すため、呼び出し側で判定しやすくなっています。

・関数を使って処理するコード例

Sub CopyDataUsingSafeLastRow()

    Dim sourceSheet As Worksheet
    Dim outputSheet As Worksheet
    Dim lastRow As Long
    Dim firstDataRow As Long

    Set sourceSheet = ThisWorkbook.Worksheets("データ")
    Set outputSheet = ThisWorkbook.Worksheets("出力")

    firstDataRow = 2

    lastRow = GetLastUsedRow(sourceSheet)

    If lastRow < firstDataRow Then
        MsgBox "コピー対象のデータがありません。"
        Exit Sub
    End If

    ' データ範囲を出力シートへコピーする
    sourceSheet.Range("A" & firstDataRow & ":C" & lastRow).Copy _
        Destination:=outputSheet.Range("A2")

    MsgBox "データをコピーしました。"

End Sub

このコードでは、最終行取得の詳細を GetLastUsedRow に任せています。

メイン処理側では、「最終行を取得する」「データがあるか確認する」「コピーする」という流れが読みやすくなります。

・関数化が実務で強い理由

関数化すると、最終行取得のルールを一箇所に集約できます。

たとえば、後から LookIn:=xlValues に変えたい場合でも、関数内だけ修正すれば済みます。

複数のマクロに同じコードを直接書いていると、修正箇所が増え、更新漏れが起きやすくなります。

また、メイン処理の見通しがよくなるため、後から見た人も処理の流れを理解しやすくなります。

実務では、「短いコード」よりも「修正しやすいコード」の方が長く使えます。

最終行取得は単体で終わることは少なく、多くの場合はループ処理と組み合わせて使用します。可変データを最後の行まで安全に処理する方法については、「【VBA】For文で最終行まで繰り返す方法｜可変データに対応する実務コード」もおすすめです。

✅ UsedRangeで最終行を取得するときの注意点

最終行取得では、UsedRange を使う方法もあります。

ただし、UsedRange は見た目のデータ範囲と一致しないことがあります。

過去に入力して削除したセル、書式だけが残っているセルなども使用範囲として残ることがあるためです。

そのため、最終行取得の目的によっては、想定より下の行を返す場合があります。

便利な反面、実務では使いどころを見極める必要があります。

・UsedRangeで最終行を取得するコード

Sub GetLastRowByUsedRange()

    Dim targetSheet As Worksheet
    Dim lastRow As Long

    Set targetSheet = ThisWorkbook.Worksheets("データ")

    With targetSheet.UsedRange
        lastRow = .Row + .Rows.Count - 1
    End With

    MsgBox "UsedRangeで取得した最終行は " & lastRow & " 行目です。"

End Sub

このコードでは、使用範囲の開始行と行数から最終行を計算しています。

見た目の表全体をざっくり把握したい場合には使えます。

・UsedRangeを過信しない方がよい理由

UsedRange は、実際に値があるセルだけでなく、過去に使われた範囲や書式が残った範囲の影響を受けることがあります。

そのため、次のような場合は注意が必要です。

下の行に書式だけ残っている
過去に入力したデータを削除した
一度広い範囲に罫線や色を設定した
CSV貼り付け後に不要行を削除した

このようなシートでは、見た目では100行目までしかデータがないのに、UsedRange では1000行目まで使われていると判断されることがあります。

最終行を厳密に取得したい場合は、Find や基準列を使う方法の方が向いています。

UsedRange は便利ですが、書式だけ残っているセルや削除済みデータの影響を受けることがあります。UsedRangeの特徴や実務での注意点について詳しく知りたい方は、「【VBA】UsedRangeプロパティ：最終行と最終列数を取得する方法と実務での注意点」もご覧ください。

✅ 空白を含む最終行取得で失敗しない判断基準

最終行取得には複数の方法があります。

大切なのは、どれか一つを万能と考えないことです。

End(xlUp)、Find、複数列判定、UsedRange は、それぞれ得意な場面が違います。

実務では、データの形に合わせて取得方法を選ぶ必要があります。

ここを整理しておくと、マクロの不具合をかなり減らせます。

・最終行取得方法の使い分け

方法	向いている場面	注意点
End(xlUp)	基準列が必ず埋まる表	基準列に空白があるとズレる
Find	表全体から最終行を探したい場合	検索条件を明示する
複数列判定	対象列が決まっている表	対象列の選定が必要
UsedRange	使用範囲をざっくり把握したい場合	書式だけのセルを拾うことがある

・実務では最初に表のルールを確認する

コードを書く前に、次の点を確認しておくと安全です。

どの列に必ず値が入るか
途中に空白行があり得るか
数式で空白表示しているセルがあるか
表の外にメモや注意書きがあるか
見出し行とデータ開始行はどこか

この確認をせずに最終行取得を書くと、データが少ないうちは問題なくても、運用が進んだ後に不具合が出ることがあります。

特に、他の人が入力するファイルや、CSVから取り込むファイルでは、想定外の空白が入りやすいため注意が必要です。

✅ まとめ：VBAで空白を含む最終行を正しく取得するには基準を決めることが重要

VBAで空白を含む最終行を正しく取得するには、単に End(xlUp) を使うだけでは不十分な場合があります。

今回のポイントをまとめます。

End(xlUp) は指定した列の最終行を取得する方法
基準列に空白があると、表全体の最終行とズレることがある
表全体から探す場合は Find を使うと安全
複数列を基準にすると、対象範囲を明確にしながら取得できる
データ開始行を変数化すると、見出しだけの表を判定しやすい
最終行取得は関数化すると再利用しやすい
UsedRange は便利だが、書式だけのセルを拾うことがある
実務ではデータの形に合わせて取得方法を選ぶことが大切

最終行取得は、コピー、転記、集計、削除など多くのVBA処理の土台になります。

空白を含むデータでも安定して処理できるように、基準列・表全体・複数列のどれを使うべきかを判断しながら設計していきましょう。