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

If you are trying to prepare for an interview check out these VBA Interview Questions and Answers. And if you found this tutorial helpful, you can support us to create more tutorials like this.