Last Row or Cell in a Non-Contiguous Range {NOT a Worksheet search}

Question

To start off, this is not the typical newbie question. I'm trying to find the last row (or cell) in a range I already created properly.

In this example I have two ranges combined into one with union. Looping through the range, I get the data I want and things work great.

Sub Test()

    Dim unionRange As Range, currentRow As Range
    Set unionRange = Union(Range("A3:C5"), Range("A8:C11"))

    For Each currentRow In unionRange.Rows
        Debug.Print "Row: " & currentRow.row
    Next currentRow

End Sub

Which gives these results

Row: 3
Row: 4
Row: 5
Row: 8
Row: 9
Row: 10
Row: 11

But when I want to try to determine if the current row is the last row in my range, I can't seem to find a clean way to get that information.

Sub Test()

    Dim unionRange As Range, currentRow As Range
    Set unionRange = Union(Range("A3:C5"), Range("A8:C11"))

    For Each currentRow In unionRange.Rows
        Dim lastRowIndex As Integer

        'lastRowIndex = unionRange. ???

        If currentRow.row = lastRowIndex Then
            MsgBox ("Last Row")
        End If

    Next currentRow

End Sub

The reason it's so hard is that every solution I've tried just produces information in the first continuous group, as shown below.

    Debug.Print unionRange.Rows.Count ' 3 (should be 7)
    Debug.Print unionRange.Rows(unionRange.Rows.Count).row ' 5 (should be 11)

Besides making a second loop before this one, is there any other way to get address information on that last row?

Answer 1

Isn't the "last" row in a certain range determined by the fact that the next row is empty? In this case the following should do the trick:

Sub Test()

Dim unionRange As Range, currentRow As Range
Set unionRange = Union(Range("A3:C5"), Range("A8:C11"))

For Each currentRow In unionRange.Rows
    Dim lastRowIndex As Integer

    If currentRow.Cells(1, 1).Offset(1, 0).Value = vbNullString Then
        MsgBox "Last Row in " & currentRow.Row
    End If

Next currentRow

End Sub

Update:

Thanks to @AndASM I just learned something new today!! I didn't know anything about Areas! So, please don't mark my answer as the correct one (even if this solves your problem).

Yet, I would like to provide the following code because I believe that this might be more of what you are looking for:

Sub Test()

Dim unionRange As Range, currentRow As Range, subArea As Range
Set unionRange = Union(Range("A3:C5"), Range("A8:C11"))

For Each currentRow In unionRange.Rows
    For Each subArea In unionRange.Areas
        If Not Intersect(subArea, currentRow) Is Nothing Then
            If currentRow.Row = subArea.Row + subArea.Rows.Count - 1 Then
                MsgBox "Last Row in " & currentRow.Row
            End If
        End If
    Next subArea
Next currentRow

End Sub

Answer 2

You need to use the Areas property of the range. When a range is non-contiguous, it contains multiple Areas. Each area is the sub-range. Unfortunately areas are not sorted in any way. So you need to check each area in a loop then return the maximum row.

In the below example I created a function that gives you the last row number in a non-contiguous range. The test sub prints the row number to the Immediate window in the Visual Basic for Applications editor.

Public Function GetLastRowOfNonContiguousArea(targetRange As Range) As Long
    Dim subRange As Range
    Dim maxRow As Long, areaMaxRow As Long

    maxRow = 0

    For Each subRange In targetRange.Areas
        areaMaxRow = subRange.Rows(subRange.Rows.Count).Row
        If areaMaxRow > maxRow Then maxRow = areaMaxRow
    Next subRange

    GetLastRowOfNonContiguousArea = maxRow
End Function

Public Sub test()
    Dim testRange As Range

    Set testRange = Union([A5:A9], [E20:F21], [C1:C3])

    Debug.Print GetLastRowOfNonContiguousArea(testRange)
End Sub