Excelの表は、あとからデータが増えたり減ったりします。
そのため、VBAで処理する範囲を毎回「2行目から10行目まで」のように指定すると、データが増えるたびにコードを直さなければなりません。
そこで役立つのが、値の入っている最終行を自動で取得する方法です。
この記事では、次のような表から、最後にデータが入っている行番号を取得します。
| 行 | A列：商品名 | B列：個数 |
|---|---|---|
| 1 | 商品名 | 個数 |
| 2 | りんご | 3 |
| 3 | みかん | 5 |
| 4 | ぶどう | 2 |
この表の最終行は4行目です。
VBAでは、次のコードで「4」を取得できます。
Dim lastRow As Long lastRow = Cells(Rows.Count, "A").End(xlUp).Row
少し長いコードですが、行っていることを一つずつ分ければ難しくありません。
VBAで最終行を取得するコード
最初に、最終行を取得してイミディエイトウィンドウへ表示してみましょう。
Sub GetLastRow()
Dim lastRow As Long
lastRow = Cells(Rows.Count, "A").End(xlUp).Row
Debug.Print lastRow
End Sub
サンプルの表で実行すると、次のように表示されます。
4
取得した行番号は、lastRowという変数に入れています。
lastRowは日本語にすると「最後の行」という意味なので、何を入れた変数なのかコードを見ただけで判断できます。
最終行を取得するコードの仕組み
最終行を取得するコードは、Excelの一番下から上へ向かってデータを探しています。
Cells(Rows.Count, "A").End(xlUp).Row
長いコードに見えますが、次の3つに分けて考えられます。
Cells(Rows.Count, "A")でA列の一番下を指定するEnd(xlUp)でデータが見つかるまで上へ戻るRowで見つかったセルの行番号を取得する
猫がA列の一番下から上へ登り、最初にデータを見つけたセルで止まるようなイメージです。
Rows.Countでシートの一番下を指定する
最初に、A列の一番下にあるセルを指定します。
Cells(Rows.Count, "A")
Rows.Countでは、Excelのシートにある行の数を取得します。
現在のExcelには1,048,576行あるため、上のコードは次のセルを指定したことになります。
Cells(1048576, "A")
つまり、A列の1,048,576行目にあるセルです。
Rows.CountがA列のデータ数を数えているわけではありません。Excelの一番下にある行番号を取得し、その行のA列を指定しています。
End(xlUp)でデータのあるセルまで戻る
A列の一番下を指定したら、データが見つかるまで上へ戻ります。
End(xlUp)
xlUpは、上方向を表しています。
そのため、A列の一番下から上へ移動し、最初に値が入っているセルで止まります。
サンプルの表ではA4セルに「ぶどう」が入っているため、A列の一番下から上へ移動するとA4セルで止まります。
Rowで行番号を取得する
End(xlUp)で取得できるのは、A4というセルです。
今回はセルに入っている「ぶどう」ではなく、行番号の「4」が必要なので、最後にRowを付けます。
.Row
これでA4セルの行番号である「4」を取得できます。
最終行を調べる列を変更する方法
基本コードの"A"は、最終行を調べる列です。
lastRow = Cells(Rows.Count, "A").End(xlUp).Row
B列の最終行を調べたい場合は、"A"を"B"へ変更します。
lastRow = Cells(Rows.Count, "B").End(xlUp).Row
列は数字でも指定できます。
lastRow = Cells(Rows.Count, 2).End(xlUp).Row
数字で指定する場合は、次のように数えます。
| 列 | 指定する数字 |
| A列 | 1 |
| B列 | 2 |
| C列 | 3 |
| D列 | 4 |
初心者のうちは、どの列を調べているのか分かりやすい"A"や"B"で指定しても問題ありません。
別シートの最終行を取得する方法
Cellsの前にシートを指定しない場合は、現在選択しているシートが対象になります。
別シートの最終行を取得する場合は、対象となるシートを明確に指定しましょう。
Sub GetLastRowOtherSheet()
Dim ws As Worksheet
Dim lastRow As Long
Set ws = ThisWorkbook.Worksheets("商品一覧")
lastRow = ws.Cells(ws.Rows.Count, "A").End(xlUp).Row
Debug.Print lastRow
End Sub
このコードでは、マクロが保存されているExcelファイルの「商品一覧」シートを取得しています。
Set ws = ThisWorkbook.Worksheets("商品一覧")
最終行を取得するときも、CellsとRows.Countの両方にwsを付けています。
lastRow = ws.Cells(ws.Rows.Count, "A").End(xlUp).Row
これにより、別のシートを開いている状態でも「商品一覧」シートの最終行を取得できます。
取得した最終行の使い方
最終行を取得する目的は、行番号を画面に表示することだけではありません。
データが入っている行まで繰り返したり、表の一番下へ新しいデータを追加したりするときに利用できます。
データのある最終行まで繰り返す
次のコードでは、2行目から最終行まで、A列に入っている値を順番に表示します。
Sub ShowAllProducts()
Dim lastRow As Long
Dim rowIndex As Long
lastRow = Cells(Rows.Count, "A").End(xlUp).Row
For rowIndex = 2 To lastRow
Debug.Print Cells(rowIndex, "A").Value
Next rowIndex
End Sub
表のデータが4行目から100行目まで増えても、コードを書き換える必要はありません。
最終行の1行下にデータを追加する
最終行に1を足すと、次にデータを追加できる行になります。
Sub AddProduct()
Dim lastRow As Long
Dim nextRow As Long
lastRow = Cells(Rows.Count, "A").End(xlUp).Row
nextRow = lastRow + 1
Cells(nextRow, "A").Value = "もも"
Cells(nextRow, "B").Value = 4
End Sub
サンプルの表では最終行が4行目なので、次の5行目に「もも」と個数の「4」が追加されます。
最終行までの範囲を指定する
最終行を使えば、データが入っている範囲を自動で指定できます。
Sub SelectProductRange()
Dim lastRow As Long
lastRow = Cells(Rows.Count, "A").End(xlUp).Row
Range("A2:B" & lastRow).Select
End Sub
ただし、実際の処理では範囲を選択せず、そのままコピーや計算を行うこともできます。
セルのコピー方法については、次の記事で詳しく紹介しています。
VBAで最終列を取得する方法
最終行と同じ考え方で、値が入っている最終列も取得できます。
Sub GetLastColumn()
Dim lastColumn As Long
lastColumn = Cells(1, Columns.Count).End(xlToLeft).Column
Debug.Print lastColumn
End Sub
最終行ではシートの一番下から上へ移動しました。
最終列では、シートの一番右から左へ移動します。
Cells(1, Columns.Count)で1行目の一番右にあるセルを指定するEnd(xlToLeft)で値が見つかるまで左へ移動するColumnで見つかったセルの列番号を取得する
たとえば、1行目のC列まで値が入っていれば、取得結果は「3」です。
別シートの最終列を取得するときは、最終行と同じように対象のシートを指定します。
lastColumn = ws.Cells(1, ws.Columns.Count).End(xlToLeft).Column
最終行を正しく取得できない原因
コードに間違いがなくても、調べる列やセルの状態によって、期待した最終行を取得できないことがあります。
よくある原因を確認しておきましょう。
基準にする列が間違っている
End(xlUp)で取得できるのは、指定した列の最終行です。
次の表では、A列の最終行は5行目ですが、B列の最終行は4行目です。
| 行 | A列：商品番号 | B列：備考 |
| 1 | 商品番号 | 備考 |
| 2 | 001 | 人気 |
| 3 | 002 | 期間限定 |
| 4 | 003 | おすすめ |
| 5 | 004 |
B列を基準にすると、5行目には値がないため「4」が返されます。
表全体の最終行を取得したい場合は、商品番号や日付など、すべてのデータ行に必ず値が入る列を基準にしてください。
途中に空白がある
End(xlUp)はシートの一番下から上へ探すため、列の途中に空白があっても、その下に値があれば最後の値まで取得できます。
たとえば、A3セルが空白でもA5セルに値が入っていれば、最終行は「5」です。
一方、Range("A1").End(xlDown).Rowのように上から下へ探す方法は、途中の空白で止まることがあります。
基本的には、この記事で紹介しているEnd(xlUp)を使用しましょう。
データが入っていない列を指定している
指定した列にデータが一つも入っていない場合は「1」が返されます。
lastRow = Cells(Rows.Count, "A").End(xlUp).Row
A列が完全に空でも、Excelには1行目があるためです。
最終行が必ず2行目以降になるとは限らないので、必要に応じてデータの有無も確認します。
If WorksheetFunction.CountA(Columns("A")) = 0 Then
MsgBox "A列にデータがありません。"
Exit Sub
End If
対象シートを指定していない
次のコードは、現在選択しているシートの最終行を取得します。
lastRow = Cells(Rows.Count, "A").End(xlUp).Row
別シートのデータを処理したい場合は、対象シートを指定してください。
lastRow = ws.Cells(ws.Rows.Count, "A").End(xlUp).Row
Cellsだけでなく、Rows.Countにも同じシートを指定するのがポイントです。
見た目は空白でも数式が入っている
セルに次のような数式が入っていると、画面上では空白に見えることがあります。
=IF(B2="","",B2*2)
表示結果が空文字でもセルには数式が入っているため、End(xlUp)ではデータのあるセルとして扱われます。
「値が表示されている最後の行」と「数式が入っている最後の行」は異なる場合があります。
まずは、どちらを最終行として取得したいのかを確認しましょう。数式による空白を除外する処理はコードが複雑になるため、通常の表では必ず値が入る列を基準にする方法がおすすめです。
まとめ
VBAで最終行を取得するときは、次のコードを使用します。
lastRow = Cells(Rows.Count, "A").End(xlUp).Row
コードの動きは次のとおりです。
- A列の一番下にあるセルを指定する
- 値が見つかるまで上へ移動する
- 見つかったセルの行番号を取得する
別シートを対象にする場合は、シートを明確に指定します。
lastRow = ws.Cells(ws.Rows.Count, "A").End(xlUp).Row
最終行を正しく取得するには、すべてのデータ行に値が入っている列を基準にすることが大切です。
取得した最終行は、繰り返し処理、コピー、計算、データの追加など、さまざまな処理に利用できます。
最終行を使ってSUM関数の計算範囲を自動で指定する方法は、次の記事で詳しく紹介しています。
