VBA Read Values from Range to an Array in Excel with Examples

VBA Read Values from Range to an Array in Excel. We can read values from Range, Cell, or Table to Arrays. Using Range we can read multiple values from one column or from multiple columns or rows. Cell can contain either single value or multiple values. Table also can consists of multiple values in multiple columns or rows. Let us see how to read values from range in different ways in the following tutorial.

Table of Contents:

Objective
Macro code to Read Values from Range(Single Column) to an Array
VBA code to Read Values from Range(Multiple Columns) to an Array
Macro code to Read Values from Range(Single Cell) to an Array
Instructions to Run VBA Macro Code
Other Useful Resources

Macro code to Read Values from Range(Single Column) to an Array

Here is the example macro code to Read Values from Range(Single Column) to an Array. In this code we have used range object to define range, UBound is used to set array upper bound and Array is used to store read values from range. It is an example for single dimensional array.

'Read Values from Range(Single Column) to an Array
Sub VBA_Read_Values_from_Range_to_Array_Single_Column()
    
    'Variable Declaration
    Dim aArrayList() As Variant
    Dim iRowNum As Integer, iColNum As Integer
    
    'Assign range to a variable
    aArrayList = Range("B1:B5")
    
    'Loop Through Rows
    For iRowNum = 1 To UBound(aArrayList)
        'Display result in Immediate window
        Debug.Print aArrayList(iRowNum, 1)
    Next iRowNum

End Sub

Output :You can see output on the immediate window. Here is the screenshot of the above macro code output.

Read Values from Single Column to an Array

VBA code to Read Values from Range(Multiple Columns) to an Array

Let us see the example macro code to Read Values from Range(Multiple Columns) to an Array. It is an example for Multi dimensional array.

'Read Values from Range(Multiple Columns) to an Array
Sub VBA_Read_Values_from_Range_to_Array_Multiple_Columns()
    
    'Variable Declaration
    Dim aArrayList() As Variant
    Dim iRowNum As Integer, iColNum As Integer
    
    'Assign range to a variable
    aArrayList = Range("A1:D4")
    
    'Loop Through Rows
    For iRowNum = 1 To UBound(aArrayList, 1)
        'Loop Through Columns
        For iColNum = 1 To UBound(aArrayList, 2)
            'Display result in Immediate window
            Debug.Print aArrayList(iRowNum, iColNum)
        Next iColNum
    Next iRowNum

End Sub

Output : You can output on the immediate window. Here is the screenshot of output.

Macro code to Read Values from Range(Single Cell) to an Array

Here is the example macro code to Read Values from Range(Single Cell) to an Array.

'Read Values from Range(Single Cell) to an Array
Sub VBA_Read_Values_from_Range_to_Array_Single_Cell()
    
    'Variable Declaration
    Dim aArrayList() As Variant
    Dim rRange As Range
    Dim iRowNum As Integer, iColNum As Integer
    
    'Define Cell Range
    Set rRange = Range("A1")
    
    'ReDefine Array size
    ReDim aArrayList(1 To 1, 1 To 1)
    
    'Store Cell Range to An Array
    aArrayList(1, 1) = rRange
    
    'Display Message on the Screen
    Debug.Print rRange
    
End Sub

Output : You can output on the immediate window. Here is the screenshot of output.