VBA Range to an Array

Assign the range’s values to a Variant in one line: arr = Range("A1:C100").Value. The result is always a two-dimensional array that starts at 1, even for a single column, so read a value with arr(row, column), as in arr(5, 2) for the fifth row, second column.

Loop through it with UBound for each dimension, then write it back in one step:

Dim arr As Variant, r As Long, c As Long
arr = Worksheets("Data").Range("A1:C100").Value
For r = 1 To UBound(arr, 1)
    For c = 1 To UBound(arr, 2)
        If IsEmpty(arr(r, c)) Then arr(r, c) = 0
    Next c
Next r
Worksheets("Data").Range("A1:C100").Value = arr

Reading the whole range at once, working on the array and writing it back is much faster than reading cell by cell. To write somewhere else, size the target to match: Range("E1").Resize(UBound(arr, 1), UBound(arr, 2)).Value = arr.

A single cell doesn’t return an array. arr = Range("A1").Value gives a plain value, so UBound(arr) stops with a type mismatch, and assigning it to a variable declared as arr() fails the same way. Declare the variable as Variant without brackets, and check If IsArray(arr) Then when the range size can vary.

Table of Content hide

Steps to Add a Range into an Array in VBA

First, you need to declare a dynamic array using the variant data type.
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.
After that, assign the range where you have value to the array.
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

What is VBA

More on VBA Arrays

Written by

Puneet Gogia

Microsoft MVP — Excel · Founder, Excel Champs

Twelve years of teaching Excel, and a decade before that using it as a data analyst. Every tutorial here is written from a file I actually built.

3M+ readers helped

1,000+ tutorials written

Since 2015 teaching Excel