【ExcelVBA】 最終行・最終列の取得（上下左右方向・UsedRange・CurrentRegion）

2019.05.19ExcelVBA,セルの操作

テスト対象のテーブル

テスト対象テーブルを使いたい場合は、↓テーブルをコピーしてエクセルの A1 に貼り付けてください。

クリップボードにコピー

最終行の探索

下方向に最終行を探索

Sub Sample()

  Dim MaxRow As Long
  
  MaxRow = ThisWorkbook.Worksheets("Sheet1").Cells(1, 1).End(xlDown).Row
  
  Debug.Print "最終行： " & MaxRow

End Sub

結果： 最終行： 6

上方向に最終行を探索

Sub Sample()

  Dim MaxRow As Long
  
  MaxRow = ThisWorkbook.Worksheets("Sheet1").Cells(Rows.Count, 1).End(xlUp).Row
  
  Debug.Print "最終行： " & MaxRow

End Sub

結果： 最終行： 6


※Rows.Count は、シートの最大行数を返します。下記は、シートの最大行数を表示

Sub Sample()

  Debug.Print Rows.Count

End Sub

最大行数： 1048576 (Excel 2010の場合)

最終列の探索

右方向に最終列を探索

Sub Sample()
 
  Dim MaxCol As Long
   
  MaxCol = ThisWorkbook.Worksheets("Sheet1").Cells(1, 1).End(xlToRight).Column
   
  Debug.Print "最終列： " & MaxCol
 
End Sub

結果： 最終列： 3

左方向に最終列を探索

Sub Sample()
 
  Dim MaxCol As Long
   
  MaxCol = ThisWorkbook.Worksheets("Sheet1").Cells(1, Columns.Count).End(xlToLeft).Column
   
  Debug.Print "最終列： " & MaxCol
 
End Sub

結果： 最終列： 3


※Columns.Count は、シートの最大列数を返します。下記は、シートの最大列数を表示

Sub Sample()

  Debug.Print Columns.Count

End Sub

最大列数： 16384 (Excel 2010の場合)

最終行と最終列を一緒に探索

使用済みセルの範囲から最終行と最終列を探索(UsedRangeプロパティ)

Sub Sample()

  Dim MaxRow As Long
  Dim MaxCol As Long
  
  With ThisWorkbook.Worksheets("Sheet1").UsedRange.SpecialCells(xlLastCell)
    MaxRow = .Row
    MaxCol = .Column
  End With
  
  Debug.Print "最終行: " & MaxRow & "  最終列: " & MaxCol

End Sub

結果： 最終行: 6 最終列: 3

※UsedRange.SpecialCells(xlLastCell)は、使用済み範囲の中で最後に位置するセルの位置を返します。

結果： 最終行: 7 最終列: 5

※UsedRangeを使うにあたって、テーブル以外の書き込みがある場合は要注意！
※テーブルのみ指定したい場合は、次の解説「テーブル範囲から最終行と最終列を探索」参照

テーブル範囲から最終行と最終列を探索(CurrentRegionプロパティ)

Sub Sample()
  
  Dim MaxRow As Long
  Dim MaxCol As Long
  
  With ThisWorkbook.Worksheets("Sheet1")
    .Activate
    .Cells(1, 1).Activate 'テーブル内のどこかをアクティブにする
  End With
  
  With ActiveCell.CurrentRegion(ActiveCell.CurrentRegion.Count)
    MaxRow = .Row
    MaxCol = .Column
  End With

  Debug.Print "最終行: " & MaxRow & "  最終列: " & MaxCol

End Sub

結果： 最終行: 6 最終列: 3

非表示行やオートフィルタの不可視行に、最終行が存在する場合の探索

「非表示」と「オートフィルタの不可視行」は、プロパティが異なるため、最終行の取得方法も異なります。

オートフィルタの不可視行に最終行がある場合の探索

Sub Sample()

  Dim MaxRow As Long
   
  MaxRow = ThisWorkbook.ActiveSheet.AutoFilter.Range.Rows.Count
   
  Debug.Print "最終行： " & MaxRow

End Sub

※上記は非表示行には対応しておりません。（オートフィルタの不可視行のみに対応）

①テスト対象のテーブル


②２行目の商品以外は全部フィルタ


結果：最終行 6

※ちゃんと、不可視行の中にある 6行目のみかん にヒット

非表示行やフィルタの不可視行が混在するシート上の最終行を探索

Sub Sample()

  Dim TmpRow1 As Long, TmpRow2 As Long
  
  TmpRow1 = ActiveSheet.Cells(Rows.Count, 1).End(xlUp).ROW
  TmpRow2 = ActiveSheet.UsedRange.Rows.Count
  
  Dim i As Long, x As Long
  
  x = TmpRow1
  For i = TmpRow1 To TmpRow2
    If IsEmpty(ActiveSheet.Cells(i, 1)) = False Then
      x = i
    End If
  Next
  
  Debug.Print "TmpRow1: " & TmpRow1
  Debug.Print "TmpRow2: " & TmpRow2
  Debug.Print "最終行: " & x

End Sub

①テスト対象のテーブル

※15行目に最終行の目標となる文字があるとします。

②一部の行を非表示にする


③非表示にしました


④商品の一部をフィルタしました


⑤5行目～25行目がFor-Nextの対象です


⑥結果：最終行: 15