空腹おやじのログと備忘録

VBA（主にExcel）でいろいろな実験的な事とか、Linuxのコマンドとか設定とかについて忘れないように、あれこれと・・・

Unionで纏められたRange内のセルにVBAでアクセスする（Excel）

Excel VBA Range Union プログラミング

あまり使う事はないような気がするが、複数のセル範囲の集合をひとつのRangeとした時、そのRange内のデータにどうやってアクセスするのか？やってみました。
複数のセル範囲が不連続の場合のアクセス方法を調べることが目的です。

Unionで纏める記事はそれなりにあるようですが、纏めた後にどうやってアクセスするのか書かれているサイトは多くはないようです。

まずは、以下のようなデータを用意し、赤で囲まれた部分と青で囲まれた部分を対象のRangeとすることとします。

f:id:Z1000S:20190301221055j:plain
サンプルデータ

結論から書くと

対象Range.Areas(n).Cells(row, col)

といった指定をすることで出来ます。

ポイントとなるのは、Range.Areas プロパティです。
これは、「複数領域選択範囲内のすべての範囲を表す**Areas** コレクションを返します。 読み取り専用です。」と説明されています。

複数のセル範囲の集合をひとつのRangeとして設定する

Unionメソッドを使用し、下記のように使用します。

Set r = Union(Sheet1.Range("B2:D4"), Sheet1.Range("F3:G4"))

Range("B2:D4") <===赤で囲まれた範囲
Range("F3:G4") <===青で囲まれた範囲

セル範囲数を調べる

使うのはAreas.Count プロパティです。

下記コードでは、

r.Areas.Count

の部分が該当し、今回の場合、２が返ってきます。

各セル範囲のアドレスを調べる

使うのは、Range.Address プロパティです。
セル範囲は、Areasに１から始まるインデックスにより指定します。
下記コードでは、

r.Areas(k).Address(False, False)

の部分が該当します。
今回の場合、
赤で囲まれた範囲がArea(1)
青で囲まれた範囲がArea(2)
となり、
r.Areas(1).Address(False, False)は
B2:D4
r.Areas(2).Address(False, False)は
F3:G4
が返ります。
Unionで指定した順番に返ってくるようです。（そうでないと困ります。）
ちなみに、Unionした全体の範囲は

r.Address(False, False)

で取得でき、
B2:D4,F3:G4
と返ってきます。

各セル範囲の行数、列数を調べる

r.Areas(n)が通常のRangeを返すので、それぞれ以下のように指定して取得することが出来ます。

r.Areas(n).Rows.Count
r.Areas(n).Columns.Count

セルの値を取得する

Range.Cells プロパティを使用して、指定したセルにアクセスできます。

r.Areas(n).Cells(i, j).Value

ここで、Cellsに指定する行、列の値は、対象Range（各Area(n)とも）の左上のセルを１行目、１列目とした相対的指定になるようで、Cells(1,1)といった指定を行うようです。
これに関しては、サンプルコードと実行結果を見ていただいた方がわかりやすいと思います。
さらに、対象Rangeの行数、列数を超える値をCellsに指定してもエラーにはならないようです。
指定方法を間違えると、対象Rangeに含まれないセルにアクセスすることになるので注意が必要です。
例えば、

? r.Areas(1).Cells(1, 4).Value

とすると
E2
と出力され、Area(1)の列は、B～Dであるにもかかわらず、Area(1)には含まれないセルE2が参照することが出来てしまいます。

サンプルコード

Public Sub AccessToUnionRange()

    Dim r   As Range
    Dim sLine   As String
    Dim i   As Long
    Dim j   As Long
    Dim k   As Long

    '複数のRangeをUnionで纏める
    Set r = Union(Sheet1.Range("B2:D4"), Sheet1.Range("F3:G4"))

    Debug.Print "エリア数：" & r.Areas.Count
    Debug.Print "全エリア：" & r.Address(False, False)

    For k = 1 To r.Areas.Count
        Debug.Print "エリア" & CStr(k) & "：" & r.Areas(k).Address(False, False)
    Next k

    For k = 1 To r.Areas.Count
        Debug.Print "----- Area" & CStr(k) & " -----"

        For i = 1 To r.Areas(k).Rows.Count
            sLine = CStr(i) & ":"

            For j = 1 To r.Areas(k).Columns.Count
                sLine = sLine & vbTab & r.Areas(k).Cells(i, j).Value
            Next j

            Debug.Print sLine
        Next i
    Next k

End Sub

実行結果

Call AccessToUnionRange
エリア数：2
全エリア：B2:D4,F3:G4
エリア1：B2:D4
エリア2：F3:G4
----- Area1 -----
1: B2 C2 D2
2: B3 C3 D3
3: B4 C4 D4
----- Area2 -----
1: F3 G3
2: F4 G4

最後に

Areas(n)を指定することで、個別のセル範囲を特定することが出来、かつRange.Areas(n)がRangeオブジェクトと分かってしまえば、それほど難しくなく処理を書けそうです。