excelareasvba

How can I refer to the nth area in a non-contigious Excel range?

Assume I selected the range A1:B2,C3:C5. Excel screenshot showing selection of a non-contiguous range

Using VBA, I can loop through each area like this:

Dim iArea As Range
For Each iArea In Application.Selection.Areas
    Debug.Print iArea.Address
Next iArea

I am able to return each range's address, in this case $A$1:$B$2 and $C$3:$C$5.

How can I refer to the 2nd area in a range, or better yet, the nth area? I can refer to the nth cell in a range using Range.Cells(cellNumberToReturn). Is something similar for ranges?

Solution

  • Just use indexer:

    Sub F()

    Dim rng As Range
    Dim rngArea As Range

    Set rng = Range("A1:B2,D3:E5")
    Set rngArea = rng.Areas(2)
    MsgBox rngArea.Address(0, 0) 'Shows D3:E5

End Sub