Excel VBA: Union fill range

[Rudi]

Hi,

The following code does not do what I expect?
I'd expect the union range A1:A3 and E1:E3 to be filled with colour, but instead, it fills A1:A6?

This is only test code illustrating the issue. The real macro I am working on is populating an array with values from the union, but it does not move the the top of the second range once it hits the bottom of the first column. Like this example it just continues down the first column and my array is not filled with the correct values?
TX for assistance...

Code: Select all

Sub Test()
Dim rgUnion As Range
Dim i As Integer
    Set rgUnion = Union(Range("A1:A" & 3), Range("E1:E" & 3))
    For i = 1 To rgUnion.Cells.Count
            rgUnion.Cells(i).Interior.Color = vbRed
    Next i
End Sub

[HansV]

This is because the range isn't contiguous. Cells(i) considers only the first area of the range.

The fastest way to color the cells is to replace the For ... Next loop with

Code: Select all

    rgUnion.Interior.Color = vbRed

If you prefer a loop, you can use

Code: Select all

Sub Test()
    Dim rgUnion As Range
    Dim rgCell As Range
    Set rgUnion = Union(Range("A1:A" & 3), Range("E1:E" & 3))
    For Each rgCell In rgUnion
        rgCell.Interior.Color = vbRed
    Next rgCell
End Sub

or, if you prefer to complicate things:

Code: Select all

Sub Test()
    Dim rgUnion As Range
    Dim rgArea As Range
    Dim i As Long
    Set rgUnion = Union(Range("A1:A" & 3), Range("E1:E" & 3))
    For Each rgArea In rgUnion.Areas
        For i = 1 To rgArea.Cells.Count
            rgArea.Cells(i).Interior.Color = vbRed
        Next i
    Next rgArea
End Sub

or even

Code: Select all

Sub Test()
    Dim rgUnion As Range
    Dim i As Long
    Dim j As Long
    Set rgUnion = Union(Range("A1:A" & 3), Range("E1:E" & 3))
    For i = 1 To rgUnion.Areas.Count
        For j = 1 To rgUnion.Areas(i).Cells.Count
            rgUnion.Areas(i).Cells(j).Interior.Color = vbRed
        Next j
    Next i
End Sub

[Rudi]

TX for the options ...

[Doc.AElstein]

Hi
I only recently realized that a Range fundamentally is a collection of Areas and need not be continuous. I too stumbled on it when applying something to a Range got through Union and it only “worked” on the first Range. ( Some things do some things do not )
I could not fill an Array() with a range got through Union directly with

Let ArrOut() =rgUnion.Value

Which rather upset me once.
(_. I could then do that using the Union Ranges and .Index in its 4 argument form for a nice “One Liner” to get multiple areas into an Array in one go…
ArrOut() = Application.Index(rgUnion, rws(), clms(), rAreas())
Or rather someone did it for me: Post # 17 http://www.mrexcel.com/forum/excel-ques ... umn-2.html" onclick="window.open(this.href);return false;
_.. but those 3 Arrays of indices can look very interesting and a challenge to get mathematically rather than Hard copying )
_................................

As I do not like implicit defaults anyway, I often write something like this now

Let rng.Areas.Item(1).Value = 1

In place of where mostly one would write this

rng.Areas.Value = 1

Or just this

rng.Areas = 1

Alan
;)