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

  • 18
    Like
  • 0
    Comment
More than 1 year has passed since last update.

はじめに

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

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

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

CurrentRegion とは

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

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

サンプル1

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

2014-12-01_21h03_06.png

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などを使って目的の処理を行えばいいですね!

サンプル2

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

2014-12-01_21h04_17.png

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が強力な武器になってくれることでしょう。