Union of ranges

[NerdyGirl]

Hi, I'm attempting to define a range based on the union of two ranges.

Code:

Dim rUnion As Range, r1 As Range, r2 As Range

Set r1 = Range("GJ7:GP7")
Set r2 = Range("GS7")
Set rUnion = Union(r1, r2)

Debug.Print rUnion(1).Address
Debug.Print rUnion(2).Address
Debug.Print rUnion(3).Address
Debug.Print rUnion(4).Address
Debug.Print rUnion(5).Address
Debug.Print rUnion(6).Address
Debug.Print rUnion(7).Address
Debug.Print rUnion(8).Address
Debug.Print r2.Address

The above rUnion addresses print correctly until rUnion(8) which returns $GJ$8. The r2.Address correctly prints $GS$7. I cannot figure out why/how the union references GJ8. Thoughts?

 

[CalcSux78]

Are there any merged cells within your worksheet? Sometimes that can throw things off.

 

[NerdyGirl]

Not in that range.

 

[MARK858]

Reading the link below might help....

Cells Within Ranges

 

[Marcelo Branco]

The ranges are treated as two separate ranges, accessible via Range.Areas property.

Try something like this

Sub Test()
    Dim rUnion As Range, r1 As Range, r2 As Range
    
    Set r1 = Range("GJ7:GP7")
    Set r2 = Range("GS7")
    Set rUnion = Union(r1, r2)
    
    Debug.Print rUnion.Areas(1)(1).Address
    Debug.Print rUnion.Areas(1)(2).Address
    Debug.Print rUnion.Areas(1)(3).Address
    Debug.Print rUnion.Areas(1)(4).Address
    Debug.Print rUnion.Areas(1)(5).Address
    Debug.Print rUnion.Areas(1)(6).Address
    Debug.Print rUnion.Areas(1)(7).Address
    Debug.Print rUnion.Areas(2)(1).Address
    Debug.Print r2.Address


End Sub

Hope this helps

M.