vba Loop over a non-contiguous range

Question

I have a non-contiguous range on rows (example address of myRange: $2:$2,$4:$205,$214:$214) and I would like to access a specific row and column within the range. I have tried the following:

'Get the value of the 2nd row, 1st column within the range

myRange.rows(2).Cells(, 1).Value

However, this is giving me the value of the 2nd row in the WorkSheet, and NOT in the range - meaning it is giving me address $3$1 - and not $4$1

Can someone please explain how I can access the values within in my range? (It may have to do with different areas)

Thank You

Answer 1

I think what you are wanting VBA to do is to see your non-contiguous range as a contiguous one. I don't think the approach that you are taking will work. You will have to treat this like multipe contiguous ranges. The following code should get you started. Where rowSelection is the row in your range that you are interested in. If you enter 2, it will select row 4 in the workbook as it is the second row in your range.

Sub Macro1()

    Dim rowCounter As Long
    Dim rowSelection As Long

    rowSelection = 2
    For Each Rng In Range("A2:A2,A4:A205,A214:A214").Areas
         If Rng.Rows.Count >= rowSelection Then
            Rng.Rows(rowSelection - rowCounter).Cells(1, 1).Select
            End
         Else
            rowCounter = rowCounter + Rng.Rows.Count
         End If
    Next Rng

End Sub

Answer 2

Here are my entries - not necessarily better than Irwin's

Function GetValue(rInput As Range, Row As Long, Column As Long) As Variant

    Dim rArea As Range
    Dim lCumRows As Long
    Dim lActualRow As Long

    For Each rArea In rInput.Areas
        lCumRows = lCumRows + rArea.Rows.Count
        If Row <= lCumRows Then
            lActualRow = rArea.Rows(1).Row + (Row - (lCumRows - rArea.Rows.Count + 1))
            Exit For
        End If
    Next rArea

    If lActualRow > 0 Then
        GetValue = rInput.Parent.Cells(lActualRow, Column).Value
    End If

End Function

Function GetValue2(rInput As Range, Row As Long, Column As Long) As Variant

    Dim rRow As Range
    Dim lRowCnt As Long

    For Each rRow In rInput.Rows
        lRowCnt = lRowCnt + 1
        If lRowCnt = lrow Then
            GetValue2 = rRow.Cells(1, Column).Value
            Exit For
        End If
    Next rRow

End Function

And go read http://www.dailydoseofexcel.com/archives/2004/07/07/the-strange-object/ for some insight as to why Excel is behaving that way.

And the test proc if you're interested

Sub test()

    Dim myRange As Range

    Set myRange = Union(Rows(2), Range("4:205"), Rows(214))

    Debug.Print GetValue(myRange, 1, 2), GetValue(myRange, 1, 2)
    Debug.Print GetValue(myRange, 2, 2), GetValue(myRange, 2, 2)
    Debug.Print GetValue(myRange, 3, 2), GetValue(myRange, 3, 2)
    Debug.Print GetValue(myRange, 200, 2), GetValue(myRange, 200, 2)

End Sub

Answer 3

Thank you everyone for their answers -- Before saw these answers I figured it out myself and so far it is working. I wont say it is the most efficent method but seems to work:

 Public Function NextRow(index As Integer, rows As Range) As Range
    Dim i As Integer, r As Range
    i = 1
    Set NextRow = Nothing
    For Each r In rows.rows
        If i = index Then
            Set NextRow = Range(r.Address)
            Debug.Print "NextRow: " & NextRow.Address
            Exit Function
        End If

        i = i + 1
    Next r

End Function

It seems similar 2nd answer - baically I am advancing to range to index I want to work with and than I return a range set by the address (!important)

I than call it like this:

NextRow(2, myRange).Cells(,1).value