Creating an Array from a Range in VBA
ArraysExcelVbaRangeArrays Problem Overview
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.
Arrays Solutions
Solution 1  Arrays
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.
Solution 2  Arrays
If we do it just like this:
Dim myArr as Variant
myArr = Range("A1:A10")
the new array will be with two dimensions. Which is not always somehow comfortable to work with:
To get away of the two dimensions, when getting a single column to array, we may use the builtin Excel function “Transpose”. With it, the data becomes in one dimension:
If we have the data in a row, a single transpose will not do the job. We need to use the Transpose function twice:
Note: As you see from the screenshots, when generated this way, arrays start with 1, not with 0. Just be a bit careful.
Edit June.2021:
In newer versions of Excel, the function is: Application.WorksheetFunction.Transpose()
Solution 3  Arrays
Using Value2
gives a performance benefit. As per Charles Williams blog
>Range.Value2 works the same way as Range.Value, except that it does not check the cell format and convert to Date or Currency. And thats probably why its faster than .Value when retrieving numbers.
So
DirArray = [a1:a5].Value2
Bonus Reading

Range.Value: Returns or sets a Variant value that represents the value of the specified range.

Range.Value2: The only difference between this property and the Value property is that the Value2 property doesn't use the Currency and Date data types.
Solution 4  Arrays
This function returns an array regardless of the size of the range.
Ranges will return an array unless the range is only 1 cell and then it returns a single value instead. This function will turn the single value into an array (1 based, the same as the array's returned by ranges)
This answer improves on previous answers as it will return an array from a range no matter what the size. It is also more efficient that other answers as it will return the array generated by the range if possible. Works with single dimension and multidimensional arrays
The function works by trying to find the upper bounds of the array. If that fails then it must be a single value so we'll create an array and assign the value to it.
Public Function RangeToArray(inputRange As Range) As Variant()
Dim size As Integer
Dim inputValue As Variant, outputArray() As Variant
' inputValue will either be an variant array for ranges with more than 1 cell
' or a single variant value for range will only 1 cell
inputValue = inputRange
On Error Resume Next
size = UBound(inputValue)
If Err.Number = 0 Then
RangeToArray = inputValue
Else
On Error GoTo 0
ReDim outputArray(1 To 1, 1 to 1)
outputArray(1,1) = inputValue
RangeToArray = outputArray
End If
On Error GoTo 0
End Function
Solution 5  Arrays
In addition to solutions proposed, and in case you have a 1D range to 1D array, i prefer to process it through a function like below. The reason is simple: If for any reason your range is reduced to 1 element range, as far as i know the command Range().Value will not return a variant array but just a variant and you will not be able to assign a variant variable to a variant array (previously declared).
I had to convert a variable size range to a double array, and when the range was of 1 cell size, i was not able to use a construct like range().value so i proceed with a function like below.
Public Function Rng2Array(inputRange As Range) As Double()
Dim out() As Double
ReDim out(inputRange.Columns.Count  1)
Dim cell As Range
Dim i As Long
For i = 0 To inputRange.Columns.Count  1
out(i) = inputRange(1, i + 1) 'loop over a range "row"
Next
Rng2Array = out
End Function
Solution 6  Arrays
Adding to @Vityata 's answer, below is the function I use to convert a row / column vector in a 1D array:
Function convertVecToArr(ByVal rng As Range) As Variant
'convert two dimension array into a one dimension array
Dim arr() As Variant, slicedArr() As Variant
arr = rng.value 'arr = rng works too (https://bettersolutions.com/excel/cellsranges/vbaworkingwitharrays.htm)
If UBound(arr, 1) > UBound(arr, 2) Then
slicedArr = Application.WorksheetFunction.Transpose(arr)
Else
slicedArr = Application.WorksheetFunction.index(arr, 1, 0) 'If you set row_num or column_num to 0 (zero), Index returns the array of values for the entire column or row, respectively._
'To use values returned as an array, enter the Index function as an array formula in a horizontal range of cells for a row,_
'and in a vertical range of cells for a column.
'https://usefulgyaan.wordpress.com/2013/06/12/vbatrickoftheweekslicinganarraywithoutloopapplicationindex/
End If
convertVecToArr = slicedArr
End Function
Solution 7  Arrays
I'm another vote for iterating through the cells in the range. Unless somebody has found a workaround, my experience trying to assign the range directly to a Variant has been that it works fine (albeit returning a 2dimensional array when I really only need 1D) except if my range has multiple areas, like for example, when I want just the visible cells in a column of a filtered table, or if I have ctrlselected different blocks of cells on a sheet.
Iterating through all the cells in the range with a for..each loop always produces the results I expect.
Public Function RangeToArray(ByRef myRange As Range)
Dim i As Long
Dim individualCell As Range
ReDim myArray(myRange.Count  1)
For Each individualCell In myRange
myArray(i) = individualCell.Text ' or maybe .Value
i = i + 1
Next
RangeToArray = myArray
End Function
I wanted to add this as a comment to Paolo's answer since it's pretty similar but I am a newbie and don't have enough reputation, so here's another slightly different answer.
Solution 8  Arrays
Transpose is a great advice. I have multiple arrays in my app. Some global, some local, some loaded from ranges and some created programatically.
I had numerous problems with dimensioning. Now, with transpose they are all one dimension.
I did have to modify code slightly, because one version runs on Excel 2003 and another (slower) on 2010.
Caution: You will have to Transpose the array again, when saving it to a range.