VBA Read Values from Range to an Array

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("A1:A4")
    
    'Loop Through Rows
    For iRowNum = 1 To UBound(aArrayList)
        'Display result in Immediate window
        Debug.Print aArrayList(iRowNum, 1)
    Next iRowNum

End Sub

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

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

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

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

Instructions to use Macro

Here are the instructions to use above macro procedure in Visual basic editor.

  • Open Visual Basic Editor(VBE) by clicking Alt +F11
  • Go to code window by clicking F7
  • Copy above specified macro or procedure
  • Paste above copied code in code window
  • Run macro by clicking F5 or Run command
  • You can see output on the screen
  • Find above output screenshot of the specified procedure.

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

Related VBA Array articles

You may also like the related VBA Array articles.

VBA Array Articles




Leave a Comment

Your email address will not be published. Required fields are marked *