【エクセルVBA】最終行を正しく取得できない理由と解決法まとめ

こんにちは、自動化エンジニアをしています。kozuです。

エクセルVBAで頻繁に必要になる処理として「最終行の取得」があります。シートの表を最終行まで繰り返し処理をしたり、セル範囲をコピーしたりする際に使います。

ネットで検索するといくつかの方法が出てきますが、前提の置き方により実装方法が異なるため、何も考えずにコピペして使用すると誤動作する可能性があります。よくある取得方法と注意点を解説しようと思います。また、一手間加えることで正確に取得できる方法も紹介します。

１－１．列のセルがブランクになるまでの最終行（End(xlDown)）

指定したセルの「End(xlDown).Row」で指定セルの下に向かってブランクになる手前までの行数を取得することができます。ブランクセルの下に値があるセルがあった場合は無視されます。

Sub 最終行取得()
  Dim lastRow As Long
  lastRow = ThisWorkbook.Sheets("Sheet1").Range("A1").End(xlDown).Row
  
  MsgBox "最終行：" & lastRow
End Sub

データにブランクが存在しないことがわかっている場合はこの方法でよいでしょう。

ちなみに、最終行の値を変数に格納していますが、データ型は「Integer」ではなく「Long」にしています。Integer型とLong型の値の範囲は以下の通りです。シートの最終行はInteger型の最大値32767を超えているため、Longを使用するようにしましょう。

Integer型：-32,768 ～ 32,767
Long型 ：-2,147,483,648 ～ 2,147,483,647

１－２．End(xlDown) の注意点

「End(xlDown)」は最も有名な方法ですが、注意点があります。それは、2行以上あることを前提にしていることです。以下の表を例にA～C列で取得できる値を確認してみます。

Sub 最終行取得()
   Dim lastRowA As Long
   Dim lastRowB As Long
   Dim lastRowC As Long
   lastRowA = ThisWorkbook.Sheets("Sheet1").Range("A1").End(xlDown).Row
   lastRowB = ThisWorkbook.Sheets("Sheet1").Range("B1").End(xlDown).Row
   lastRowC = ThisWorkbook.Sheets("Sheet1").Range("C1").End(xlDown).Row
   
   MsgBox "A列の最終行：" & lastRowA & vbLf & _
       "B列の最終行：" & lastRowB & vbLf & _
       "C列の最終行：" & lastRowC
End Sub

C列は正しい行数を取得できていますが、A列とB列は「1048576」となり、間違った値になりました。これは、セルを選択し「Ctrl & Down」キーで止まったセルの行が取得されるため、指定したセルの下に値があるセルがないとシートの最終行になってしまうことが原因です。

１－３．End(xlDown) で２行未満を考慮した実装

関数「GetLastRow」として外出ししました。シートオブジェクトと列を指定して使用します。1行目スタートを前提としているため表が空行の下にある場合はもう少し考慮が必要ですが、レアケースであるため十分足りるでしょう。

Sub 最終行取得()
   Dim lastRowA As Long
   Dim lastRowB As Long
   Dim lastRowC As Long
   lastRowA = GetLastRow(ThisWorkbook.Sheets("Sheet1"), "A")
   lastRowB = GetLastRow(ThisWorkbook.Sheets("Sheet1"), "B")
   lastRowC = GetLastRow(ThisWorkbook.Sheets("Sheet1"), "C")
   
   MsgBox "A列の最終行：" & lastRowA & vbLf & _
       "B列の最終行：" & lastRowB & vbLf & _
       "C列の最終行：" & lastRowC
End Sub

'最終行取得
Function GetLastRow(ByVal sheet As Worksheet, ByVal col As String) As Long
   Dim lastRow As Long
   
   With sheet
       If .Range(col & 1).Value = "" Then
           '1行目の値がブランクの場合は最終行は0
           lastRow = 0
       ElseIf .Range(col & 2).Value = "" Then
           '2行目の値がブランクの場合は最終行は1行
           lastRow = 1
       Else
           lastRow = .Range(col & 1).End(xlDown).Row
       End If
   End With
   
   GetLastRow = lastRow
End Function

２－１．列全体の最終行（End(xlUp)）

上記はセルがブランクになるまでの行数を取得しましたが、途中にブランクが存在することを考慮した方法があります。

指定したセルの「End(xlUp).Row」で指定セルの上に向かって値があるセルまでの行数を取得することができます。セルを「"A" & Rows.Count」で指定していますが、「Rows.Count」はシートの最大行を指しています。シートを指定していないためアクティブシートが対象になります。最大行はどのシートでも変わらないのでアクティブシートからの取得で問題ありません。

Sub 最終行取得2()
   Dim lastRow As Long
   lastRow = ThisWorkbook.Sheets("Sheet1").Range("A" & Rows.Count).End(xlUp).Row
   
   MsgBox "最終行：" & lastRow
End Sub

２－２．End(xlUp) の注意点

「End(xlUp)」の注意点は、1行以上あることを前提にしていることです。以下の表を例にA～C列で取得できる値を確認してみます。

Sub 最終行取得2()
   Dim lastRowA As Long
  Dim lastRowB As Long
  Dim lastRowC As Long
  lastRowA = ThisWorkbook.Sheets("Sheet1").Range("A" & Rows.Count).End(xlUp).Row
  lastRowB = ThisWorkbook.Sheets("Sheet1").Range("B" & Rows.Count).End(xlUp).Row
  lastRowC = ThisWorkbook.Sheets("Sheet1").Range("C" & Rows.Count).End(xlUp).Row
  
  MsgBox "A列の最終行：" & lastRowA & vbLf & _
      "B列の最終行：" & lastRowB & vbLf & _
      "C列の最終行：" & lastRowC
End Sub

B、C列は正しい行数を取得できていますが、A列は「1」となり、間違った値になりました。これは、セルを選択し「Ctrl & Up」キーで止まったセルの行が取得されるため、1行目がブランクでも1行目が選択されることが原因です。

２－３．End(xlUp) で1行目がブランクの場合を考慮した実装

関数「GetLastRow2」として外出ししました。シートオブジェクトと列を指定して使用します。

Sub 最終行取得2()
   Dim lastRowA As Long
  Dim lastRowB As Long
  Dim lastRowC As Long
  lastRowA = GetLastRow2(ThisWorkbook.Sheets("Sheet1"), "A")
  lastRowB = GetLastRow2(ThisWorkbook.Sheets("Sheet1"), "B")
  lastRowC = GetLastRow2(ThisWorkbook.Sheets("Sheet1"), "C")
  
  MsgBox "A列の最終行：" & lastRowA & vbLf & _
      "B列の最終行：" & lastRowB & vbLf & _
      "C列の最終行：" & lastRowC
End Sub

'最終行取得
Function GetLastRow2(ByVal sheet As Worksheet, ByVal col As String) As Long
   Dim lastRow As Long
   
   With sheet
       If .Range(col & Rows.Count).End(xlUp).Row = 1 And .Range(col & 1).Value = "" Then
           '1行目の値がブランクの場合は最終行は0
           lastRow = 0
       Else
           lastRow = .Range(col & Rows.Count).End(xlUp).Row
       End If
   End With
   
   GetLastRow2 = lastRow
End Function

３－１．表全体の最終行（CurrentRegion）

最終行が全ての列で揃っていない場合、列を指定せずに最終行を取得したいことがあります。

指定したセルの「CurrentRegion.Value」で指定したセルの連結領域の値を配列で取得し「UBound(data)」で配列の長さを最終行として取得することができます。ちなみに、「UBound(data)」は「UBound(data, 1)」と同じになり、dataは２次元配列のため「UBound(data, 2)」で最終列を取得することができます。

Sub 最終行取得3()
   Dim lastRow As Long
   
   Dim data As Variant
   data = ThisWorkbook.Sheets("Sheet1").Range("A1").CurrentRegion.Value
   lastRow = UBound(data)
   
   MsgBox "最終行：" & lastRow
End Sub

３－２．CurrentRegion の注意点

「CurrentRegion」の注意点は、表の領域が分裂していないことを前提にしていることです。以下の表で取得できる値を確認してみます。

B8セルに値がありますが、A1セルの領域とは別の領域にあるため対象外になります。列の位置はわからないが表のキーとなる列には値が全行あるという前提であれば問題ありません。

分離した領域を含んだ表全体の最終行の取得方法は以下で解説します。

４．シート全体の最終行（UsedRange）

指定したセルの「UsedRange.Value」で指定したシートに値があるセルの値を配列で取得し「UBound(data)」で配列の長さを最終行として取得することができます。シート全体を取得するため、セルの指定はありません。

Sub 最終行取得4()
   Dim lastRow As Long
   
   Dim data As Variant
   data = ThisWorkbook.Sheets("Sheet1").UsedRange.Value
   lastRow = UBound(data)
   
   MsgBox "最終行：" & lastRow
End Sub

４－２．UsedRange.Value の注意点

列を指定せずブランクも含めた最終行を取得できますが、シートにゴミがないことを前提にしています。誤ったキー入力によりシートの見えない範囲に値を入力したり、値の削除を忘れてしまった場合に余計な行まで参照してしまうことで誤動作の原因になってしまいます。

まとめ

最終行の取得について解説してきました。表のデータについて前提を意識し、目的に合った方法で実装するようにすることで誤動作のを防ぐことができます。