CODEWITHSUNDEEP
arraysvbaexcelrange
AngeloBE
AngeloBE

Reputation: 1

VBA Fill 2D array with distinct ranges

I want to fill a 2D array with several named ranges that are not connected. The contain one column and a constant number of rows

Code:

Range("Range1").Select
LastRow = Selection.Rows.count

Dim data() as variant
Redim data(1 to 4,1 to LastRow)
data(1)=Range("Range1").value
data(2)=Range("Range2").value
data(3)=Range("Range3").value
data(4)=Range("Range4").value

It gives a subject out of range error, but I cannot work out why?

Upvotes: 0

Views: 102

Answers (1)

CallumDA
CallumDA

Reputation: 12113

You need to reference both dimensions of your variant array. For example:

data(1, 1) = Range("Range1").value

As per your comments, I'd suggest you use a Collection instead

Dim data As Collection
Set data = New Collection

data.Add Range("Range1")
data.Add Range("Range2")
...

MsgBox data(1).Address 'returns the address of Range1

Upvotes: 1

Related Questions