【Excel VBA入門 第23回】データの最終行を取得しよう！End(xlUp)・CurrentRegion・UsedRangeの使い分け

イントロダクション

前回（第22回）で、ブック・ファイル操作編が完結した。集計マクロでは、各ブックのA1セルとB1セルを読み取っていたが、これは「読み取る場所が決まっている」から成り立つ作り方である。

実務で扱う表は、行数が日々増えたり減ったりする。たとえば、毎日1行ずつ売上が追加される表に対して、「A2からC8まで」のようにセル範囲をコードに直接書いてしまうと、行が増えたとたんに、新しい行が処理の対象から漏れてしまう。

そこで今回から3回にわたり、「表データの処理編」をご紹介しよう。

• 第23回（今回）：データの最終行を取得する
• 第24回：表を1行ずつ処理する（条件に合う行を抽出して転記する）
• 第25回：配列を使って、処理を高速化する

今回は、その土台となる「最終行の取得」を学ぶ。最終行が分かれば、行数が変わる表でも、最後のデータまでを過不足なく処理できる。

準備：サンプルの表を用意する

今回使うのは、次のような表である。

1行目が見出しで、データは2行目から8行目までの7件である。「売上データ」というシート名で、マクロ有効ブックに用意する。

手で入力してもよいが、次のマクロを実行すれば、この表が自動で作られる。マクロ有効ブックの標準モジュールに貼り付けて、実行しよう。

Sub サンプルデータを作る()
    Dim ws As Worksheet
    Dim 商品 As Variant
    Dim 売上 As Variant
    Dim i As Long

    ' 「売上データ」シートがあれば使い、なければ新しく作る
    On Error Resume Next
    Set ws = ThisWorkbook.Worksheets("売上データ")
    On Error GoTo 0

    If ws Is Nothing Then
        Set ws = ThisWorkbook.Worksheets.Add
        ws.Name = "売上データ"
    Else
        ws.Cells.ClearContents
    End If

    商品 = Array("りんご", "みかん", "ぶどう", "バナナ", "メロン", "いちご", "もも")
    売上 = Array(1200, 800, 1500, 600, 2000, 900, 1100)

    ' 見出しと、7件のデータを書き込む
    ws.Range("A1:C1").Value = Array("日付", "商品", "売上")
    For i = 0 To 6
        ws.Cells(i + 2, 1).Value = DateSerial(2026, 10, i + 1)
        ws.Cells(i + 2, 2).Value = 商品(i)
        ws.Cells(i + 2, 3).Value = 売上(i)
    Next i
    ws.Range("A2:A8").NumberFormat = "m/d"
End Sub

「売上データ」シートがすでにある場合は、中身を消してから書き込むので、そのシートにデータがないことを確認してから実行しよう。
日付は、DateSerial で作った日付データを入力し、m/d の形式で表示している。
なお、このマクロは、何度実行しても同じ表に戻せる。実験のあとで元に戻したいときにも使える。

【方法1】End(xlUp)で最終行を求める

最終行を求める方法として、最もよく使われるのが End(xlUp) である。シートの一番下のセルから、上に向かって最初にデータが入っているセルを探す、という考え方である。

Sub 最終行を調べる()
    Dim ws As Worksheet
    Dim 最終行 As Long

    Set ws = ThisWorkbook.Worksheets("売上データ")
    最終行 = ws.Cells(ws.Rows.Count, 1).End(xlUp).Row

    MsgBox "最終行は " & 最終行 & " 行目です。"
End Sub

実行すると、「最終行は 8 行目です。」と表示される。
ws.Cells(ws.Rows.Count, 1).End(xlUp).Row を、部分に分けて見ていこう。

第7回では、Range("A1").End(xlToRight) で、右方向の終端セルを選択した。
今回は、方向を xlUp（上）に変えて、一番下のセルから探しているだけで、考え方は同じである。

行番号は Long で受け取る

最終行を入れる変数は、Integer ではなく Long で宣言する。Integer が扱えるのは、32,767までだからである。シートの行数はそれを超えるので、Long を使うのが基本である。

どの列を基準にするか

Cells(ws.Rows.Count, 1) の「1」は、基準にする列番号（A列なら1、B列なら2）である。必ずデータが入っている列を選ぼう。基準の列に空白のセルがあると、その下にデータがあっても、実際より小さい行番号になってしまう。

データが1件もないとき

A列に何もデータがないシートで実行すると、End(xlUp) は1行目（A1）を返すため、最終行は「1」になる。見出しだけの表と、データのない表を区別したいときは、最終行が「2未満」かどうかで判定するとよい（これは、後ほどの Function の例で使う）。

【方法2】CurrentRegionで表全体の範囲を取得する

次は、表全体の範囲を一度に取得できる CurrentRegion である。CurrentRegion は、指定したセルを含む、空白の行と列で囲まれた連続範囲を返す（キーボードの Ctrl + Shift + * で選択される範囲と同じである）。

Sub 表の範囲を調べる()
    Dim ws As Worksheet
    Dim 表 As Range

    Set ws = ThisWorkbook.Worksheets("売上データ")
    Set 表 = ws.Range("A1").CurrentRegion

    MsgBox "表の範囲：" & 表.Address & vbCrLf & _
           "行数（見出しを含む）：" & 表.Rows.Count & vbCrLf & _
           "最終行：" & (表.Row + 表.Rows.Count - 1)
End Sub

実行すると、「表の範囲：$A$1:$C$8」「行数（見出しを含む）：8」「最終行：8」と表示される。

最終行は、「表の先頭の行番号（表.Row）＋ 行数 － 1」で求められる。
表の最終行だけでなく、範囲そのものを Range オブジェクトとして取得できるので、表全体をコピーしたり、書式を設定したりするときに便利である。

【方法3】UsedRangeで使用済みの範囲を取得する

3つ目は、UsedRange である。これは、シートの中でデータや書式が設定されたセルを含む、使用済みの範囲を返す。

Sub 使用範囲を調べる()
    Dim ws As Worksheet
    Dim 範囲 As Range

    Set ws = ThisWorkbook.Worksheets("売上データ")
    Set 範囲 = ws.UsedRange

    MsgBox "使用範囲：" & 範囲.Address & vbCrLf & _
           "最終行：" & (範囲.Row + 範囲.Rows.Count - 1)
End Sub

こちらも、「使用範囲：$A$1:$C$8」「最終行：8」となる。

ただし、UsedRange には注意点がある。
データが入っていなくても、背景色や罫線などの書式が設定されたセルは、使用範囲に含まれてしまう。

実際に試してみよう。A20のセルに、データは入力せずに、塗りつぶしの色だけを設定して、もう一度「使用範囲を調べる」を実行してみる。最終行が「20」になるはずである。

同じ状態で、方法1の「最終行を調べる」を実行すると、「8」のままである。

確認が終わったら、A20の色を消しておこう。
なお、色を消しても、使用範囲がすぐに元に戻らないことがある。

3つの方法の比較

3つの方法の特徴を、整理しておこう。

実務では、「最終行だけが知りたい」場面が多いので、まず End(xlUp) を使うのが基本である。

途中に空白行がある表で試してみよう

方法による違いが一番はっきり出るのが、表の途中に空白の行があるときである。試しに、5行目（A5:C5）の内容を削除してみよう。

この状態で、方法1と方法2を、それぞれ実行しよう。

方法1（End(xlUp)）：最終行は「8」のまま。下から探すので、空白行の影響を受けない

方法2（CurrentRegion）：範囲は $A$1:$C$4、最終行は「4」になる。空白行のところで、表が途切れてしまう

「表の途中に、空白行が混ざる可能性がある」場合は、CurrentRegion ではなく、End(xlUp) を使うほうが、安全であることが分かる。
確認が終わったら、5行目のデータ（10/4、バナナ、600）を元に戻しておこう。
Ctrl＋Zで元に戻すか、準備で作った サンプルデータを作る を、もう一度実行すればよい。

最終行をFunctionにまとめる

最終行を求める処理は、何度も使うことになる。そこで、第12回で学んだ Function にまとめておこう。

Function 最終行を取得する(ws As Worksheet, 列番号 As Long) As Long
    最終行を取得する = ws.Cells(ws.Rows.Count, 列番号).End(xlUp).Row
End Function

シートと、基準にする列番号を渡すと、最終行を返す Function である。
使うときは、次のようにする。第19回で学んだ事前チェックの考え方で、データがない場合の確認も入れてみよう。

Sub 最終行を取得して確認する()
    Dim ws As Worksheet
    Dim 最終行 As Long

    Set ws = ThisWorkbook.Worksheets("売上データ")
    最終行 = 最終行を取得する(ws, 1)

    If 最終行 < 2 Then
        MsgBox "データがありません。", vbExclamation
        Exit Sub
    End If

    MsgBox "データは " & (最終行 - 1) & " 件です。（最終行：" & 最終行 & "）"
End Sub

見出しが1行あるので、データの件数は「最終行 － 1」である。

最終行を使って、範囲を指定する

最終行が分かると、行数が変わる表でも、範囲を指定できる。"C2:C" & 最終行 のように、文字列をつなげて、セル範囲を作る。売上の合計を求めてみよう。

Sub 売上を合計する()
    Dim ws As Worksheet
    Dim 最終行 As Long
    Dim 合計 As Long

    Set ws = ThisWorkbook.Worksheets("売上データ")
    最終行 = 最終行を取得する(ws, 1)

    合計 = Application.WorksheetFunction.Sum(ws.Range("C2:C" & 最終行))
    MsgBox "売上の合計：" & 合計
End Sub

サンプルの表（7件）では、「売上の合計：8100」と表示される。表の最後に、9行目として、データを1行追加してから実行してみよう。行を追加しても、マクロを書き換えずに、合計が正しく変わることが確認できる。

まとめ

今回は、データの最終行を取得する方法を学んだ。

• 最終行は、ws.Cells(ws.Rows.Count, 列番号).End(xlUp).Row で求められる
• 行番号を入れる変数は、Long で宣言する
• 基準にする列は、必ずデータが入っている列を選ぶ
• CurrentRegion は、表全体の範囲を取得できるが、途中に空白行があると途切れる
• UsedRange は、書式だけのセルも含まれることがある
• 「最終行だけが知りたい」ときは、End(xlUp) を基本にする
• "C2:C" & 最終行 のように、文字列をつなげて、範囲を指定できる

次回（第24回）は、今回求めた最終行を使って、表を1行ずつ処理する方法を解説する。For で最終行まで繰り返し、If で条件に合う行だけを、別のシートに抽出して転記するマクロを作ってみよう。