How to access Nth cell in non-contiguous range?

[NigelTufnel]

Suppose I have a range consisting of not-necessarily contiguous cells in the same columnn, e.g.,

Set rMyRange = Range("$A$1:$A$3, $A$8, $A$10")

How do I directly access the Nth cell in the range?

For example, I tried rMyRange.cells(4).address...but I get $A$4 instead of $A$8, which is what I want.

I was able to pick off the fourth item using a For loop (e.g.,

For each rMyCell in rMyRange

But this is tedious. Is there a way to directly read the fourth cell?

 

[mikerickson]

No. Most properties, like .Item and .Cells, act as if applied to the top left cell of a range, discontinous or otherwise.

A loop is needed. In a general case, one might loop through Areas rather than Cells

Function NthCell(someRange As Range, cellSought As Long) As Range
    Dim cCount As Long
    Dim oneArea As Range
    
    For Each oneArea In someRange.Areas
        If oneArea.Cells.Count < cellSought - cCount Then
            cCount = cCount + oneArea.Cells.Count
        Else
            Set NthCell = oneArea.Item(cellSought - cCount)
            Exit Function
        End If
    Next oneArea
End Function

 

[NigelTufnel]

Mike--

Thanks for the response. It's funny how every once in awhile you find something in VBA that seems like it should be trivial, but actually requires some coding.

And thanks for supplying the code!