Areas Collection Example

When using a Range containing a number of different areas, you cannot use code like rgRange.Cells(20).Value if the 20th cell is not inside the first area in the range. This is because Excel only looks at the first area, implicitly doing rgRange.Areas(1).Cells(2 0).Value, as this example shows—with a function to provide a workaround:

Sub TestMultiAreaCells() Dim oRNg As Range 'Define a multi-area range Set oRNg = Range("D2:F5,H2:I5") 'The 12th cell should be F5.

MsgBox "Rng.Cells(12) is " & oRNg.Cells(12).Address & _

vbCrLf & "Rng.Areas(1).Cells(12) is " & oRNg.Areas(1).Cells(12).Address & vbCrLf & "MultiAreaCells(oRng, 12) is " & MultiAreaCells(oRNg, 12).Address 'The 13th cell of the multi-area range should be H2, 'that is the first cell in the second area.

MsgBox "Rng.Cells(13) is " & oRNg.Cells(13).Address & _

vbCrLf & "Rng.Areas(1).Cells(13) is " & oRNg.Areas(1).Cells(13).Address & vbCrLf & "MultiAreaCells(Rng, 13) is " & MultiAreaCells(oRNg, 13).Address End Sub

Function MultiAreaCells(oRange As Range, iCellNum As Long) As Range Dim iTotCells As Long, oArea As Range 'Loop through all the areas in the range, 'starting again from the first if we run out Do

For Each oArea In oRange.Areas 'Is the cell we want in this area? 'Return it and exit if Yes

If iTotCells + oArea.Cells.Count >= iCellNum Then

Set MultiAreaCells = oArea.Cells(iCellNum - iTotCells)

Exit Function

Else iTotCells = iTotCells + oArea.Cells.Count End If

Next Loop End Function

0 0

Post a comment