VBA Read Values from Range to an Array in Excel

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.

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
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.

VBA Read Values from Range to an Array
VBA Read Values from Range to an Array

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.

VBA Read Single Value from Cell to an Array
VBA Read Single Value from Cell to an Array

Instructions to Run VBA Macro Code or Procedure:

You can refer the following link for the step by step instructions.

Instructions to run VBA Macro Code

Other Useful Resources:

Click on the following links of the useful resources. These helps to learn and gain more knowledge.

VBA Tutorial VBA Functions List VBA Arrays in Excel Blog

VBA Editor Keyboard Shortcut Keys List VBA Interview Questions & Answers

Leave a Reply