Excel VBA - Union of Ranges and its sub ranges

Question

Consider the following vba macro in excel

Sub foo()
    Dim aRng As Range: Set aRng = ActiveSheet.Range("A1:J1")
    Dim bRng As Range: Set bRng = ActiveSheet.Range("A4:J4")
    Dim cRng As Range: Set cRng = ActiveSheet.Range("A10:J10")

    Dim uRng As Range: Set uRng = Union(aRng, bRng, cRng)
    uRng.Style = "Good"
    uRng.Cells(2, 1).Style = "Bad"
End sub

The results are: Rows 1 "A1:J1", "A4:J4", "A10:J10" are good and cell "A2" is bad. I expected cell "A4" to be bad. "A2" is not in uRng; why would it be returned by uRng.Cells(2,1)?

Other oddities: uRng.Rows.Count = 1 and uRng.Columns.Count = 10. Am I wrong to expect uRng to be 3x10 range? Or is that undefined because the positions of aRng, bRng, and cRng to each other is not specified?

Answer 1

They are actually treated as three separate ranges, accessible from uRng via the Range.Areas property:

http://msdn.microsoft.com/en-us/library/microsoft.office.interop.excel.range.areas(v=office.11).ASPX

Using your example, here's how to get A4 included, as the first cell in the second range:

Sub foo()
    Dim aRng As Range: Set aRng = ActiveSheet.Range("A1:J1")
    Dim bRng As Range: Set bRng = ActiveSheet.Range("A4:J4")
    Dim cRng As Range: Set cRng = ActiveSheet.Range("A10:J10")

    Dim uRng As Range: Set uRng = Union(aRng, bRng, cRng)
    uRng.Style = "Good"
    uRng.Areas(2).Cells(1, 1).Style = "Bad"
End Sub

also, the code below will give you same result:

Sub foo2()
    Dim uRng As Range: Set uRng = [A1:J1,A4:J4,A10:J10]
    uRng.Style = "Good"
    uRng.Areas(2).Cells(1, 1).Style = "Bad"
End Sub

Answer 2

A uRng.Cells(2, 1) works only for contiguous ranges and not non-contiguous range. To select a cell in a particular area, you have to address that area and then use .Cells(r,w)

For example

uRng.Areas(n).Cells(r, c)

Where n is the area which you want to write to and r, c are the row/column.

B. Rows.Count works with 1 Area. So to find the rows count in a non-contiguous range, you will have to loop though it.

See this example

Option Explicit

Sub foo()
    Dim aRng As Range: Set aRng = ActiveSheet.Range("A1:J1")
    Dim bRng As Range: Set bRng = ActiveSheet.Range("A4:J4")
    Dim cRng As Range: Set cRng = ActiveSheet.Range("A10:J10")
    Dim rngArea As Range
    Dim rwCount As Long

    Dim uRng As Range: Set uRng = Union(aRng, bRng, cRng)

    If uRng.Areas.Count > 1 Then
        Debug.Print "It's a non-contiguous range"
        For Each rngArea In uRng.Areas
            rwCount = rwCount + rngArea.Rows.Count
        Next
        Debug.Print "The range has " & rwCount & " rows"
    Else
        Debug.Print "It's a contiguous range"
        Debug.Print "The range has " & uRng.Rows.Count & " rows"
    End If
End Sub

C. There is an oddity in columns.count as well but since all the range had same number of columns, you didn't relaize this. Try this

Option Explicit

Sub foo()
    Dim aRng As Range: Set aRng = ActiveSheet.Range("A1:J1")
    Dim bRng As Range: Set bRng = ActiveSheet.Range("A4:K4") '<~~ I changed this.
    Dim cRng As Range: Set cRng = ActiveSheet.Range("A10:J10")

    Dim uRng As Range: Set uRng = Union(aRng, bRng, cRng)

    '~~> This will still give you 10 instead of `11`
    Debug.Print uRng.Columns.Count
End Sub

So the same logic applies to find the number of columns as well.

IMP: Now It depends on how you want to count the number of rows/columns. What if the rows/columns of the areas overlap? In such a case do you want to total count of the rows/columns or the actual Excel rows/columns in this non contiguous range?