Transfer Union(rng1,rng2) to variant array

[David]

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

 

[Bob Phillips]

dim myVar as Range
Set MyVar = union(Range("rng2"),range("rng2"))

HTH

Bob

 

[Barb Reinhardt]

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

 

[Ryan H]

In the Subject line of your post you reference rng1 and rng2, but in your
actual post you reference rng2 twice. I am assume you want to fill an one
dimensional array with the values from rng1 and rng2, right? Since you
didn't specify what type of values are in the ranges I will declare the array
variable as Variant. If ranges contain numbers you can use Double or String
if they contain text. Try this code. Hope this helps! If so, let me know,
click "YES" below.

Option Explicit

Sub FillArray()

Dim i As Long
Dim rng As Range
Dim myVar(0 To 23) As Variant

For Each rng In Union(Range("rng1"), Range("rng2"))
myVar(i) = rng.Value
i = i + 1
Next rng

End Sub

 

[OssieMac]

Hi David,

I believe this occurs because it stops at non-contiguous rows. The following
example shows that if counting the rows in a non-contiguous range the
returned value is the end of the first non-contiguous group of rows. However,
you can count Cells in non-contiguous ranges.

I think that the only way to achieve your desired results is as per the
example of Redim an array to the number of cells and read them in with a For
Each loop.

Note that I have used rnge1 and rnge2 because xl2007 says rng1 is an invalid
name.


Sub CellsToArray()

Dim myVar()
Dim cel As Range
Dim i As Long

'Counting rows stops at non contiguous rows
MsgBox Union(Range("rnge1"), _
Range("rnge2")).Rows.Count

'Counting cells works
MsgBox Union(Range("rnge1"), _
Range("rnge2")).Cells.Count

'Note Redim myVar(1 To X) is like _
using Option Base 1.
ReDim myVar(1 To Union(Range("rnge1"), _
Range("rnge2")).Cells.Count)

For Each cel In Union(Range("rnge1"), _
Range("rnge2")).Cells

i = i + 1
myVar(i) = cel.Value
Next cel

For i = 1 To UBound(myVar)
MsgBox myVar(i)
Next i

End Sub

 

[David]

Sory folks,
Thanks for your responses, all useful
The original code is OK but for my silly mistake with rng2 twice
Should have been MyVar = union(Range("rng1"),range("rng2"))
I've pressed all the 'Yes' buttons
Cheers