Method 'Union' of object '_Application' failed (Runtime Error 1004

[vivmaha]

Hi,

I'm trying to select two disjoint ranges of cells.

Here is my code:

Dim r1 As Range
Dim r2 As Range
Set r1 = Names("SS_ChurnAssums").RefersToRange.Offset(1, 0)
Set r2 = Range(Names("SS_WeightedChurn").RefersToRange.Offset(1, 0),
Names("SS_WeightedChurn").RefersToRange.Offset(i, 0))

Application.Union(r1, r2).Select ' <-- Error thrown here.

I verified that r1 and r2 are valid ranges (by calling the .select method
and checking if they were selected). So the error is in the Application.Union
line. I search tons of places for a solution, and many ppl seem to be getting
the same problem.

What am i doing wrong?
Is there another way to select multiple ranges?

Thanks.

 

[Gary''s Student]

Sub demt()
Set r1 = Range("A1")
Set r2 = Range("A2")
Application.Union(r1, r2).Select
End Sub

will work

Sub demt()
Set r1 = Sheets("Sheet1").Range("A1")
Set r2 = Sheets("Sheet2").Range("A2")
Application.Union(r1, r2).Select
End Sub

will throw a 1004 eror.


Are you sure the two ranges are on the same sheet??

 

[vivmaha]

They are in different sheets. Is it not possible to union two selections from
different worksheets?

Thanks.

 

[Jim Thomlinson]

Nope... Not possible. Woorkbooks contain worksheets and worksheets contain
ranges. The same way one sheet can not span two workbooks, one range can not
span two sheets...