VBA Range to an Array

HomeVBA TutorialArraysVBA 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.
    1-declare-a-dynamic-variable-1
  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.
    2-declare-one-more-variable-2
  3. After that, assign the range where you have value to the array.
    3-assign-the-range-3
  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.
    4-create-a-loop-to-print-all-values-4
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

More on VBA Arrays

VBA Add New Value to the Array | VBA Clear Array | VBA Loop Through an Array | VBA Search for a Value in an Array | VBA Sort Array | VBA Array Length (Size) | VBA Array with Strings | VBA Dynamic Array | ISARRAY Function | ARRAY Function | VBA Arrays