You are on the Home/Publications & Training/Case Studies/Index into a multi-area range page

Web This Site

## “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

Now, enter

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

You can test the function with