CODEWITHSUNDEEP
arraysexcelvbaunion
Kaz
Kaz

Reputation: 1332

Can you use a union range to define an array?

I'm trying to define a 2-d array using data from 2 separate columns. the code currently defines my array only as the first column (range1), ignoring the second (range2). Is this due to a mistake on my part or can I just not use a unionised (is that the right term?) range to define an array?

 Sub define_array()

Dim FTSE100() As Variant

Dim wsh As Worksheet

Dim range1 As Range
Dim range2 As Range
Dim finalrange As Range

Dim a As Integer
Dim finalrow As Integer

' define array

Set wsh = Sheet2
wsh.Activate

finalrow = Range("a1").End(xlDown).row

Set range1 = Range(Cells(1, 1), Cells(finalrow, 1))
Set range2 = Range(Cells(1, 5), Cells(finalrow, 5))
Set finalrange = Union(range1, range2)

' FTSE100 = finalrange

' Sheet15.Range("A:B") = FTSE 100

' Original code ended here

ReDim FTSE100(1 To finalrange.Areas.Count)

For a = 1 To finalrange.Areas.Count
FTSE100(a) = finalrange.Areas(a)
Next

With Sheet15.Range("A:B")
For a = 1 To .Columns.Count
.Columns(a).Resize(UBound(FTSE100(a))).Value = FTSE100(a)
Next
End With

End Sub

That seems to have done it, thanks for your help.

Upvotes: 0

Views: 1230

Answers (1)

David Zemens
David Zemens

Reputation: 53623

You can't do it quite this way with a non-contiguous range.

You could do this though:

ReDim FTSE100(1 To finalRange.Areas.Count)

For a = 1 To finalRange.Areas.Count
    FTSE100(a) = finalRange.Areas(a)
Next

You would have to do a similar For ... Next loop to write the values

With Sheet15.Range("A:B")
For a = 1 To .Columns.Count
    .Columns(a).Resize(UBound(FTSE100(a))).Value = FTSE100(a)
Next
End With

But now it dawns on me that your problem is really more simple than this: you just want to copy values from a uninionizd range to another worksheet. That's easy and doesn't require an intermediary array variable FTSE100.

Set range1 = Range(Cells(1, 1), Cells(finalrow, 1))
Set range2 = Range(Cells(1, 5), Cells(finalrow, 5))

Sheet15.Range("A:A") = range1
Sheet15.Range("B:B") = range2

Upvotes: 1

Related Questions