Excel.Union returning two Areas

[Daniel]

I am baffled, for some reason the following lines in the immidiate
window

? Excel.Union(Range("$A$1:$M$88"),Range("$A$1:$Z$2")).AddressLocal

Outputs:

$A$1:$M$88,$A$1:$Z$2

Which is a Range with two Areas. I was expecting $A$1:$Z$88, which is
what I need. How would I make sure that Union only returns Ranges with
a single Area?

Thanks,
Daniel

 

[Frank Kabel]

Hi
as you have not specified a range such as A1:Z88 with your UNION
statement why would you expect Excel would return this?. The Union
statement would return a single area if this is possible. e.g.
? Excel.Union(Range("$A$3:$Z$88"),Range("$A$1:$Z$2"))
would return
$A$1:$Z$88

 

[Bob Phillips]

Daniel,

Union is not the Range from the start of one range to the end of another.
Rather, Union returns a range object that encapsulates the ranges being
acted upon.

Thus, if these ranges have no overlapping area, the union will just return
an object that refers to the individual ranges.

Union(Range("A1"), Range("C1")) is of this type and will return a range
object of two areas.

If the ranges totally overlap (that is the smaller ones are all contained
within the largest) then the Union will return a range object the same as
the largest range.

Union(Range("H5:J10"), Range("I6:I8")) is of this type and will return a
range object for H5:J10

If they don't overlap but are contiguous (that is no columns or no rows
between them that are not in one of the ranges), and if the columns or the
rows of the ranges are identical, it will return a single range object of
the type you expect.

Union(Range("A1:C10"), Range("A6:C12")) is of this type and will return a
range object for A1:C12

 

[Tom Ogilvy]

You have received good answers on Union. Perhaps what you want is this:

? Range(Range("$A$1:$M$88"),Range("$A$1:$Z$2")).Address
$A$1:$Z$88

I believe this will give you the rectangle that can contain the two areas.

 

[Daniel]

? Range(Range("$A$1:$M$88"),Range("$A$1:$Z$2")).Address
$A$1:$Z$88

Thanks Tom (and the others). That is what I needed.