Range union in different sheet

[Emily]

Is the union function not allow to perform union two ranges in different sheet?

Thanks

 

[Juan Pablo González]

No, you can't do that, AKAIK.

 

[Emily]

Thanks

 

[Tom Urtis]

Interesting question...

Emily - - can you give an example of what you have in mind, including how the ranges would be identified what you want to do to the union if anything was possible. Not saying anything/everything is possible; just wondering what you were hoping to do.

 

[Emily]

Interesting question...

Emily - - can you give an example of what you have in mind, including how the ranges would be identified what you want to do to the union if anything was possible. Not saying anything/everything is possible; just wondering what you were hoping to do.

Hi Tom,

It just a curious question read from other forum, the code below shows error. As my understanding from Excel help, UNION only works in the same sheet. I cannot find any article about UNION two or more ranges in different sheet from WEB, so I post the question here.

Sub test()
Dim myRange1 As Range
Dim myRange2 As Range
Dim myRange As Range

Set myRange1 = Sheets("Sheet1").UsedRange
Set myRange2 = Sheets("Sheet2").UsedRange
Set myRange = Application.Union(myRange1, myRange2)
mytemp = myRange.Address(False, False)
End Sub


Regards.
Emily

 

[Tom Urtis]

Well, I don't know what you want to do to the union ranges once you define them, but this might get you started, given the clues in your last post:

Sub UnionLabor()
Dim Range1 As Range, Range2 As Range, myRange As Range
With Sheets("Sheet1")
Set Range1 = .UsedRange
End With
With Sheets("Sheet2")
Set Range2 = .UsedRange
End With
Set myRange = Range(Range1.Address & "," & Range2.Address)
MsgBox "Total areas: " & myRange.Areas.count & vbCrLf & "Total cells: " & myRange.Cells.count, 64, "For this Union range:"
End Sub


Keep in mind, what a Union returns can be misleading. Example, if your used range on Sheet1 is from A1:A10, and Sheet2 is totally blank, you will get a result of 11 cells total. Also, if a Union was done on the same sheet where one range is A1:A10 and another is A10:A20, with A10 overlapping, the Union would not take that into account and give you a false number of total cells involved.

You are generally better off manipulating noncontiguous ranges with the standard comma separator instead of invoking a Union.

 

[Emily]

Thanks Tom

Great, the impossible task cannot be solved because one have no ability to solve.

I really appreciate your help.
Thanks Again

 

[Juan Pablo González]

Tom,

Hate being the ***** here, but this line:

Set myRange = Range(Range1.Address & "," & Range2.Address)

isn't actually doing what you'd expect. Range1 is in Sheet1, ok, and Range2 is in Sheet2, so far so good.

BUT, when you say

?Range1.Address & "," & Range2.Address

you're creating a string that's NOT "sheet dependant" sort of speak...

Let's use an example

?Range1.Address
 $A$1:$C$10
?Range2.Address
 $A$1:$F$50

again, Range1 is in a different sheet than Range2, but this

?Range1.Address & "," & Range2.Address

returns

 $A$1:$C$10,$A$1:$F$50

Now, when you finally say

Set myRange = Range(stringabove)

it doesn't "know" that the first part was in Sheet1 and the second part was in Sheet2. It just assumes the ActiveSheet, because the range is not fully qualified.

You *could* get around that by using

Range1.Address(External:=True)

BUT THEN, an error arises when you try to define myRange.

I don't even think its possible to do that in Excel. If you define a name like

Rng
=Sheet1!A1:A10, Sheet2!A2:B15

Excel won't complain, but if you try to use it in a formula

=AREAS(Rng)

or just

=Rng

it will return a #VALUE! error.

So, I don't think its possible at all... I mean, I don't think its possible for ONE range object to hold different "subranges" from different sheets.

 

[Emily]

This topic really interesting.
I had added a code for testing:
myRange.Font.ColorIndex = 3

It change Range1 anf Range2 in activesheet only

Thanks ALL

 

[Tom Urtis]

Juan - -

You are absolutely correct and I misled myself into seeing 2 separate areas and total cells being recognized in the MsgBox, even though I could not manipulate the separate ranges with index color or values. I thought Excel was still recognizing them being on 2 separate sheets. I took a closer look at it tonight, and your point is quite correct. Each Range object belongs to a Parent worksheet object, so ranges on 2 separate worksheets cannot contribute to one range object.

I have this nagging feeling that somehow this should be possible. We landed on the moon 34 years ago, so why can't one range span across more than one worksheet. I know about the 3-D disallowances and Parent restrictions, but I still want to poke around the issue and do some experimenting.

Sorry Emily, for now, and maybe forever, the Union range object is still a one-sheet deal.