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

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

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

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

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


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

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

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

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

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

functionのひな形

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

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

このfunctionは、

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

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

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

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に最終行の値が格納されます。

1.RangeオブジェクトのEndプロパティを使う方法(先頭行から下に検索)

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

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

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

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

vbalastrow_1_1

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

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

欠点

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

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

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

2.RangeオブジェクトのEndプロパティを使う方法 その2(最終行から上に検索)

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

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

Private 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
vbalastrow_1_4

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

WS 指定したワークシート
WS.Cells 指定したワークシートの「すべてのセル」
WS.Cells.Rows 指定したワークシートの「すべてのセル」の属する行(=すべての行)
WS.Cells.Rows.Count 指定したワークシートの「すべてのセル」の属する行の数(=全行数)

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

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

欠点

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

vbalastrow_1_5

3.WorksheetオブジェクトのUsedRangeを使う

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

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

Private 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を使って、最終行を取得します。

vbalastrow_1_6

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

vbalastrow_1_7

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

欠点

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

vbalastrow_1_8

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

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

4.UsedRangeの結果に補正を加える方法

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

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

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

解説

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

  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」から1ずつ減らしていき、最後1になるまで処理を繰り返します。

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

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

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

WS 指定したワークシート
WS.Cells(C, CheckCol) 指定したワークシートの《「C」行「CheckCol」列のセル》
WS.Cells(C, CheckCol).Value 指定したワークシートの《「C」行「CheckCol」列のセル》の値

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

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

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

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

vbalastrow_1_10

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

欠点

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

5.UsedRangeの結果に補正を加える方法 その2

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

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

Private 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
   
   LastRow = .UsedRange.Row + .UsedRange.Rows.Count - 1
   
   If LastRow > 1 Then
    buf = .Range(.Cells(1, CheckCol), .Cells(LastRow, CheckCol)).Value
    
    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

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

2018/3/31修正:風柳さんからのご指摘を受け、「4」「5」のソースコードを修正しました。

エクセル基礎講座 「無料」動画マニュアル

「経理事務のためのエクセル基礎講座(初級編)」(動画マニュアル 総収録時間162分)を無料プレゼント中です!

このマニュアルで解説していることを一通り学べば、経理事務を行う上で最低限必要となる知識が得られます。

ご登録者の方には、合わせて、公認会計士が実体験を通して身に付けたエクセルを使う技をメールにてお伝えしていきます!

無料動画講座 登録フォーム

※ご登録頂いたメールアドレスに、エクセルを使いこなすための情報を配信するメールセミナー「エクセル倍速講座」も合わせて配信させていただきます。