Most, if not all, developers who work with Microsoft Excel have had reason to “pick” a particular cell in a range with syntax like
myRange.Cells(1)
A problem arises with the above when myRange contains multiple areas (i.e., it is not a single contiguous range) because the Cells property assumes a single-area range. Try the following quick test. In Excel, select cells A1, A5, and A8:A9 (to do so select A1, then hold the CTRL key down and select A5, then while still holding CTRL down, click and drag to select A8:A9). In the Visual Basic Editor, open the Immediate Window and enter
?selection.cells.count
The answer will be 4.
Now, enter
?selection.cells(2).address
The answer will be $A$2! Of course, we would like it to be $A$5.
The function below returns the expected cell. Further, and unlike the Cells property, if the index is greater than the number of cells in the range, it returns nothing. The code below goes in a standard module
Option Explicit
Function RangeIndex(aRng As Range, ByVal Idx As Long) As Range
'Returns the cell at aRng(Idx) taking into account _
aRng containing multiple areas, whereas Cells(Idx) _
assumes there is only one area. _
The result of this function also differs from the _
Cells(Idx) property in that if Idx > total number of _
cells in aRng, this function returns nothing.
If Idx > aRng.Cells.Count Then Exit Function
Dim AreaIdx As Long
AreaIdx = 1
Do While aRng.Areas(AreaIdx).Cells.Count < Idx
Idx = Idx - aRng.Areas(AreaIdx).Cells.Count
AreaIdx = AreaIdx + 1
If Idx >= 1 Then Set RangeIndex = aRng.Areas(AreaIdx).Cells(Idx)
End Function
You can test the function with
?rangeindex(selection,2).Address
The result will, as expected, be $A$5 and
?rangeindex(selection,3).Address
will return $A$8.
This tip provides a more reliable mechanism to identify the cell at a particular index in a multi-area range.
