VBA Range to an Array

Steps to Add a Range into an Array in VBA

1. First, you need to declare a dynamic array using the variant data type.
   
2. Next, you need to declare one more variable to store the count of the cells from the range and use that counter for the loop as well.
   
3. After that, assign the range where you have value to the array.
   
4. From here, we need to create a loop to print all the values to the immediate window so that you can see that all the values are assigned to the array.
   

Sub myArrayRange()

Dim iAmount() As Variant
Dim iNum As Integer

iAmount = Range("A1:A11")

For iNum = 1 To UBound(iAmount)
    Debug.Print iAmount(iNum, 1)
Next iNum

End Sub

And when you run the above code it shows you all the values you have assigned from the range (“A1:A11) to the array iAmount and prints it to the immediate window.

In the same way, you can also use a multi-dimensional array.

Sub myArrayRange()

Dim iAmount() As Variant
Dim iNum1 As Integer

iAmount = Range("A1:B13")

For iNum1 = 1 To UBound(iAmount, 1)
        Debug.Print iAmount(iNum1, 1) & " " & iAmount(iNum1, 2)
Next iNum1

End Sub

Or you can also do this way as well.

Sub myArrayRange()

Dim iAmount() As Variant
Dim iNum1 As Integer
Dim iNum2 As Integer

iAmount = Range("A1:B13")

For iNum1 = 1 To UBound(iAmount, 1)
    For iNum2 = 1 To UBound(iAmount, 2)
        Debug.Print iAmount(iNum1, iNum2)
    Next iNum2
Next iNum1

End Sub