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




--
Regards,

OssieMac


"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