Creating an Array from a Range in VBA

arrays excel vba range
basaltanglia picture basaltanglia · Jun 7, 2016 · Viewed 230.2k times · Source

I'm having a seemingly basic problem but can't find any resources addressing it.

Simply put, I just want to load the contents of a Range of cells (all one column) into an Array.

I am able to accomplish this by means of 

DirArray = Array(Range("A1"), Range("A2"))

But for some reason, I cannot create the array when expressed this way:

DirArray = Array(Range("A1:A2"))

My real Range is much longer (and may vary in length), so I don't want to have to individually enumerate the cells this way. Can anyone tell me how to properly load a whole Range into an Array?

With the latter code:

MsgBox UBound(DirArray, 1)

And

MsgBox UBound(DirArray)

Return 0, whereas with the former they return 1.

Answer

vacip picture vacip · Jun 7, 2016

Just define the variable as a variant, and make them equal:

Dim DirArray As Variant
DirArray = Range("a1:a5").Value

No need for the Array command.

Related questions

How to clear the entire array?

I have an array like this: Dim aFirstArray() As Variant How do I clear the entire array? What about a collection?

Read More
Excel VBA: Variants in Array Variables

A question on variants. Im aware that variants in Excel vba are both the default data type and also inefficient (from the viewpoint of overuse in large apps). However, I regularly use them for storing data in arrays that have …

Read More
How do I slice an array in Excel VBA?

What function can I use in Excel VBA to slice an array?

Read More