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


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


The answer will be 4.

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


    If Idx >= 1 Then Set RangeIndex = aRng.Areas(AreaIdx).Cells(Idx)

    End Function


You can test the function with


The result will, as expected, be $A$5 and


will return $A$8.


This tip provides a more reliable mechanism to identify the cell at a particular index in a multi-area range.