Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.programming
|
|||
|
|||
Excel.Union returning two Areas
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 |
#2
Posted to microsoft.public.excel.programming
|
|||
|
|||
Excel.Union returning two Areas
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 -- Regards Frank Kabel Frankfurt, Germany "Daniel" schrieb im Newsbeitrag om... 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 |
#3
Posted to microsoft.public.excel.programming
|
|||
|
|||
Excel.Union returning two Areas
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 -- HTH RP "Daniel" wrote in message om... 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 |
#4
Posted to microsoft.public.excel.programming
|
|||
|
|||
Excel.Union returning two Areas
You have received good answers on Union. Perhaps what you want is this:
? Range(Range("$A$1:$M$88"),Range("$A$1:$Z$2")).Addr ess $A$1:$Z$88 I believe this will give you the rectangle that can contain the two areas. -- Regards, Tom Ogilvy "Daniel" wrote in message om... 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 |
#5
Posted to microsoft.public.excel.programming
|
|||
|
|||
Excel.Union returning two Areas
"Tom Ogilvy" wrote:
? Range(Range("$A$1:$M$88"),Range("$A$1:$Z$2")).Addr ess $A$1:$Z$88 Thanks Tom (and the others). That is what I needed. |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
Get the row count of different areas in the Excel | Excel Discussion (Misc queries) | |||
SQL Union in MS Excel 2007 | Excel Discussion (Misc queries) | |||
Excel - printing specific areas | Excel Discussion (Misc queries) | |||
Bringing in a Union Query with excel? | Excel Worksheet Functions | |||
Equivalent of Minus in Excel. Also Union, Intersect. | Excel Worksheet Functions |