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

ExcelVBA, セルの操作

テスト対象のテーブル



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



商品名産地個数
りんご青森県4
みかん熊本県3
めろん茨城県5
いちご静岡県2
みかん和歌山県1

最終行の探索

下方向に最終行を探索

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
※上記は非表示行には対応しておりません。(オートフィルタの不可視行のみに対応)

①テスト対象のテーブル


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


結果:最終行 6

※ちゃんと、不可視行の中にある 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


TmpRow1: 5
TmpRow2: 25
最終行: 15

「非表示行なし、オートフィルタ設置なし」「非表示あり、オートフィルタ設置なし」 「非表示なし、オートフィルタ設置&フィルタ済み」「非表示あり、オートフィルタ設置&フィルタ済み」すべてで、 15行目が最終行になります。