“Indexing” into a multi-area range
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.
A quick workaround
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
Loop
If Idx >= 1 Then Set RangeIndex =
aRng.Areas(AreaIdx).Cells(Idx)
End Function
|
