union(Range("rng2"),range("rng2"))

Will return a range object that is the union of the two ranges. In this
case, you've got the same range name listed twice, so you aren't really
getting anywhere. Within each range you can pull out the value of each
cell, but you can't get hte value of the overall range.

Think of it this way, if rng2 is A1:A10, how can you get the value of A1:A10
all at once.

What exactly are you trying to do with these two ranges? You can do
something like this

Dim r as Excel.Range
for each r in Union(Range("Range1"),Range("Range2:))
Debug.print r.value

next r

Might you want the intersection of the two arrays? If so try this

Dim myInt as excel.range

set myInt = nothing
on error resume next
Set myInt = intersect(Range("Range1"),Range("Range2"))
on error goto 0
if not myint is nothing then
debug.print myint.count
if myint.count = 1 then debug.print myint.value
end if
--
HTH,

Barb Reinhardt



"David" wrote:

Hi
I've tried:
dim myVar as variant
MyVar = union(Range("rng2"),range("rng2")).value but no joy so far
I'm just getting rng1 in the variant array
Both rng1 and rng2 are 12 x 1 ranges
Any ideas?
Thanks