【落とし穴に注意】VBAで最終行の行番号を取得する

2018年3月16日

VBAで最終行の行番号を取得する方法を紹介します。

最終行を取得する方法は様々なブログで紹介されています。
ところが、一般的に紹介されている方法だと、正しく最終行を取得できない場合もあります。

また、正しく最終行を取得できる方法には、別のデメリットもあります。

そこで、このブログでは、様々な最終行を取得する方法を紹介し、そのメリット・デメリットを紹介していきます。

最終行を取得するfunctionを作る

冒頭でも書きましたが、最終行を取得するには、様々な方法があり、状況により差し替えたい場合も出てきます。

そのため、最終行を取得するロジックは、functionとして独立させてしまいましょう。

function化することで、最終行を取得する方法を簡単に変更できるようになります。

変更したいときには、このfunctionの内部だけを変更すれば済むようになるのです。

functionのひな形

私は、最終行を取得するfunctionを、次のような感じで定義をしています。

Function getLastRow(WS As Worksheet, Optional CheckCol As Long = 1) As Long
（ここに最終行を取得するロジックを入れる） 
End Function

このfunctionは、

• 最終行を取得したいワークシートのworksheetオブジェクト
• 最終行の判定に使う列番号

を指定すると、最終行の行番号が得られるというfunctionです。

列番号は省略することができ、指定しなかった場合には「１列目」が自動で指定されます。

functionの呼び出し方

このfunctionを呼び出したいときには、次のような感じで呼び出します。

  last_row = getLastRow(ActiveSheet)

  last_row = getLastRow(Sheets(1))

というような感じで、Worksheetオブジェクトを指定します。

列番号を指定する必要があるときには、

  last_row = getLastRow(ActiveSheet,2)

  last_row = getLastRow(Sheets(1),2)

というような感じで、Worksheetオブジェクトの後に列番号を指定をしてください。

こうすることで、last_rowに最終行の値が格納されます。

１．RangeオブジェクトのEndプロパティを使う方法（先頭行から下に検索）

最初の方法は、RangeオブジェクトのEndプロパティを使う方法です。

Sub 最終行取得テスト()
  MsgBox getLastRow(ActiveSheet)
End Sub

Function getLastRow(WS As Worksheet, Optional CheckCol As Long = 1) As Long
  getLastRow = WS.Cells(1, CheckCol).End(xlDown).Row
End Function

このようにすると、指定した列（列指定を省略した場合は1列目）の１行目から、表の下端まで移動し、その行番号を取得することができます。

次のように、列番号を指定すれば、指定した列を使って、最終行を取得することもできます。

Sub 最終行取得テスト()
  MsgBox getLastRow(ActiveSheet, 2)
End Sub

欠点

次のように表に空行があると、うまく最終行が取得できません。

Sub 最終行取得テスト()
  MsgBox getLastRow(ActiveSheet)
End Sub

また、オートフィルタや非表示行がある場合もうまく動作しません。

２．RangeオブジェクトのEndプロパティを使う方法　その２（最終行から上に検索）

２番目の方法は、最終行から上に、RangeオブジェクトのEndプロパティを使って検索をする方法です。

Sub 最終行取得テスト()
  MsgBox getLastRow(ActiveSheet)
End Sub

Function getLastRow(WS As Worksheet, Optional CheckCol As Long = 1) As Long
  getLastRow = WS.Cells(WS.Cells.Rows.Count, CheckCol).End(xlUp).Row
End Function

「WS.Cells.Rows.Count」で、次のように指定したワークシートの最終行を取得できます。

この数式を使うことで、「指定したワークシートの最終行」の「指定した列」から上に「文字が入力されているセル」を探し、その行を表示することができるのです。

先ほどと違い、表の中に空行があっても正しく最終行を取得することができます。

欠点

オートフィルタや非表示行がある場合もうまく動作しません。
たとえば、1行目～5行目まで、データが入っていても、4行目・5行目が非表示になっていると、次のように最終行を正しく取得できません。

３．WorksheetオブジェクトのUsedRangeを使う

UsedRangeを使うと、使用済みの最終行を取得することができます。

Sub 最終行取得テスト()
  MsgBox getLastRow(ActiveSheet)
End Sub

Function getLastRow(WS As Worksheet, Optional CheckCol As Long = 1) As Long
  getLastRow = WS.UsedRange.Row + WS.UsedRange.Rows.Count - 1
End Function

WorksheetオブジェクトのUsedRangeプロパティで、使用済みの領域の情報を得ることができます。
そこで、UsedRangeを使って、最終行を取得します。

具体的には、次のような計算をしています。

この方法は、非表示行があったり、オートフィルタがかかっている場合にも対応できます。

欠点

UsedRangeプロパティは、本来の最終行よりも下の行に書式が設定されていたり、行を削除した場合などに正常に動作しない場合があります。

正常に動作しないといっても、必ず最終行よりも大きい数字を取得できますので、これで実害がないようであれば、UsedRangeを使う方法で構いません。

ただし、値が入っている最終行を完璧に求めるためには適していません。

４．UsedRangeの結果に補正を加える方法

UsedRangeの結果に補正を加えて、どんな場合でも最終行を取得する方法を紹介します。

Sub 最終行取得テスト()
  MsgBox getLastRow(ActiveSheet)
End Sub

Function getLastRow(WS As Worksheet, Optional CheckCol As Long = 1) As Long
  Dim LastRow As Long
  LastRow = WS.UsedRange.Row + WS.UsedRange.Rows.Count - 1
  getLastRow = 0
    
  Dim C As Long
  For C = LastRow To 1 Step -1
   If WS.Cells(C, CheckCol).Value <> "" Then
    getLastRow = C
    Exit For
   End If
  Next
End Function

解説

以下、ソースコードの解説をしていきます。

  LastRow = WS.UsedRange.Row + WS.UsedRange.Rows.Count - 1
  getLastRow = 0

最初の行の「右辺」は、先ほどのUsedRangeを使う方法とまったく一緒ですが、その計算結果を「LastRow」変数に格納します。

また、「getLastRow」（＝このfunctionの返り値）には「0」を代入します。

後続処理で、値が入力されているセルが見つからなかったときには「0」を返り値として返すことになります。

そこで、あらかじめ、この段階で代入しておきます。

  For C = LastRow To 1 Step -1

　（繰り返し処理）


  Next

for文で「LastRow」から１ずつ減らしていき、最後１になるまで処理を繰り返します。

繰り返し処理の中身が次のソースです。

   If WS.Cells(C, CheckCol).Value <> "" Then
    getLastRow = C
    Exit For
   End If

「WS.Cells(C,CheckCol).Value」の意味は、次のとおりです。

これが空欄でない場合には、返り値「getLastRow」を「C」で置き換える、という処理を行っています。

つまり、このForループでは、

• UsedRangeで得られた「行」から
• 上に1行ずつ移動していき
• 空欄ではないセルが見つかったら
• そのセルの行番号をgetLastRowに代入する

という動作をしていることになります。

これで、非表示セルが混じっていたとしても、最終行を取得することができるのです。

欠点

この方法は、forループの中で、１つ１つセルの内容を確認していくため動作速度が若干遅くなる場合があります。
動作速度を速くするためには、配列を使って、セルの内容を一気に読み込むなどの工夫が必要です。

５．UsedRangeの結果に補正を加える方法　その２

先ほどの「動作が遅い」という欠点を補うと次のようになります。

Sub 最終行取得テスト()
  MsgBox getLastRow(ActiveSheet)
End Sub

Function getLastRow(WS As Worksheet, Optional CheckCol As Long = 1) As Long
 With WS
  getLastRow = 0
  
  If Not Intersect(.UsedRange, .Columns(CheckCol)) Is Nothing Then
   Dim LastRow As Long
   LastRow = .UsedRange.Row + .UsedRange.Rows.Count - 1
   
   If LastRow > 1 Then
    Dim buf As Variant
    buf = .Range(.Cells(1, CheckCol), .Cells(LastRow, CheckCol)).Value
    
    Dim C As Long
    For C = UBound(buf, 1) To 1 Step -1
     If Not IsEmpty(buf(C, 1)) Then
      getLastRow = C
      Exit Function
     End If
    Next
   
   ElseIf Not IsEmpty(.Cells(1, CheckCol).Value) Then
     getLastRow = 1
   End If
  
  End If
 End With
End Function

細かい解説はしませんが、セルへの参照が１回で済むので、先ほどのプログラムよりも高速に動作します。

2018/3/31修正：風柳さんからのご指摘を受け、「４」「５」のソースコードを修正しました。
2018/6/18修正：Functionの定義時の「Private」を削除、dim定義を追加

おすすめ記事

• エクセルのボタンからVBAマクロに引数を渡す方法
• 【ExcelVBA】複数ボタンの処理をマクロ1つにまとめる
• VBAで仕訳データのシート間転記を簡単にする
• 【エクセルVBA初心者用】手軽にCSVデータを作る
• 【超初心者向け】エクセルVBAでデバッグをする方法を解説します
• 【初心者向け】エクセルVBAの配列とは?配列の使いどころを解説します
• 【超初心者向き】Subプロシージャの基本を5分で理解する
• 【エクセルVBA雑学】データ型とは何か詳しく説明してみる
• 【所要時間5分】初めてのエクセルVBA-マクロを作って実行する
• 【エクセルVBA】VBAとマクロの違いは?