表データの処理にはCurrentRegionが便利

はじめに

Excel VBAを書く時、CurrentRegionという非常に便利なAPIがあるのですが、
自分の周りには知っている人がいなかったので紹介します。

Excelでドキュメントが書かれる場合、やはり表形式になることが多いと思います。
したがって、VBAでも表データを処理することが少なくないでしょう。

そんな時便利なのが「CurrentRegion」です。

CurrentRegion とは

正確には「RangeオブジェクトのCurrentRegionプロパティ」ですが、
これはざっくり言うと「表のRangeオブジェクトをいい感じに取得できるAPI」です。

実際にコードを見たほうが速いと思いますのでご覧下さい。

サンプル１

次のような表データ（赤枠内）を処理するサンプルコードです。

Main.bas

Option Explicit

Private Const SHEET_NAME As String = "Sheet1"
Private Const CELL_POS As String = "A2"

Public Sub Main()
    Dim ws As Worksheet
    Set ws = ThisWorkbook.Sheets(SHEET_NAME)
    
    Dim tbl As Range
    Set tbl = ws.Range(CELL_POS).CurrentRegion
    
    Debug.Print "表の範囲 - " & tbl.Address
    
    Set tbl = tbl.Offset(1, 1).Resize(tbl.Rows.Count - 1, tbl.Columns.Count - 1)
    
    Debug.Print "表の範囲(リサイズ後) - " & tbl.Address
    
    Dim r As Range
    Dim c As Range
    For Each r In tbl.Rows
        Debug.Print "-----"
        For Each c In r.Columns
            Debug.Print c.Value
        Next
    Next
End Sub

結果

表の範囲 - $A$2:$E$5
表の範囲(リサイズ後) - $B$3:$E$5
-----
 11 
 12 
 13 
 14 
-----
 21 
 22 
 23 
 24 
-----
 31 
 32 
 33 
 34 

以下がCurrentRegionを使っている箇所です。

Set tbl = ws.Range(CELL_POS).CurrentRegion

この場合、CELL_POSは「A2」なわけですが、
セル（A2）は「$A$2:$E$5」の範囲の表に含まれていると自動で判定され、
その範囲（$A$2:$E$5）のRangeオブジェクトを返してくれます。

仮にCELL_POSを「B3」のようにしても、同じ範囲のRangeオブジェクトが返却されます。

ただ、実際に処理したいのは赤枠の中（$B$3:$E$5）だけなのに、
これだとヘッダー部分もRangeオブジェクトに含まれてしまっています。

そこで、OffsetとResizeを使ってRangeオブジェクトの範囲を調整します。

Set tbl = tbl.Offset(1, 1).Resize(tbl.Rows.Count - 1, tbl.Columns.Count - 1)

これで目的の範囲（$B$3:$E$5）のRangeオブジェクトが取得できました。

あとはFor Eachなどを使って目的の処理を行えばいいですね！

サンプル２

もう一つサンプルを挙げてみましょう。今度は表にタイトルがある場合です。

Main.bas

Option Explicit

Private Const SHEET_NAME As String = "Sheet2"
Private Const CELL_POS As String = "A2"

Public Sub Main()
    Dim ws As Worksheet
    Set ws = ThisWorkbook.Sheets(SHEET_NAME)
    
    Dim tbl As Range
    Set tbl = ws.Range(CELL_POS).CurrentRegion
    
    Debug.Print "表の範囲 - " & tbl.Address
    
    Set tbl = tbl.Offset(2, 0).Resize(tbl.Rows.Count - 2, tbl.Columns.Count)
    
    Debug.Print "表の範囲(リサイズ後) - " & tbl.Address
End Sub

結果

表の範囲 - $A$2:$D$6
表の範囲(リサイズ後) - $A$4:$D$6

セルがタイトルの位置（A2）を示しているとして、CurrentRegionで得られるのはタイトルを含んだ表の範囲（$A$2:$D$6）です。

そこで、見出しとヘッダの2行分を縮小してやればよいですね。

Set tbl = tbl.Offset(2, 0).Resize(tbl.Rows.Count - 2, tbl.Columns.Count)

最後に

表サイズが固定されている場合は範囲を決め打ちしてもよいと思いますが、
そうでない場合は、CurrentRegionが強力な武器になってくれることでしょう。