VBA – Create Array from Excel Range – 1 Dimension

Excel Macro – Create 1D Array from Vba Array

This is one of the easy methods to initialize an array. If you convert data in worksheet directly to vba array, then it will create a 2d array by default.

If you would like to convert Excel range to Array of 1D, you could use one of these code snippets.

1. Use Transpose
2. While or For loop

The first method is direct. But it can handle only 1 row or 1 column of data. If you have a table data then use this code to convert an array of 2D to 1D.

1. VBA Transpose to Convert Range to 1D Array

For the below code to work, enter some value in Excel worksheet range A1 to A10.

Sub RangeToArray1D()
    'Declare Array as variant & Range
    Dim rArray() As Variant, rRange As Range, iRow As Double, iCol As Double
    Set rRange = ThisWorkbook.Sheets("sheet1").Range("A1:A10")
    
    'Convert Range to Array directly
    rArray = Application.Transpose(rRange.Value)
           
    'Print values in array
    Debug.Print VBA.Join(rArray, ",")
End Sub

Press F5 to run this Vba code. Then, you could view the output in immediate window (Ctrl+G).

Note: In this example data in single column is populated in a 1d array. To populate data in single row, you have to use the Transpose function twice like this.

rArray = Application.Transpose(Application.Transpose(rRange.Value))

This way, the data in column gets transposed twice & creates proper output. Otherwise, it will create a 2d array.

2. VBA Loop to Assign values to Array

This is a very basic code. It may not be as faster as previous one. But still this is flexible to filter only required values.

Sub RangeToArray1DLoop()
    'Declare Array as variant & Range
    Dim rArray() As Variant
    Dim i As Integer
    
    'Loop Thru each value in Range to create array
    For i = 0 To 9
        ReDim Preserve rArray(i)
        rArray(i) = ThisWorkbook.Sheets("sheet1").Range("A" & i + 1)
    Next
End Sub

You could insert If conditions to include any criteria while loading the data to array.