VBA Assign Range to Array

This tutorial will demonstrate how to populate an array with a range of cells.

Assign Range to Array

We can easily populate a Variant array with a range of cells.

Assign Value From a Single Column

This example will loop through Range(“A1:A10”), assigning the the cell values to an array:

Sub TestArrayValuesSingle()
'Declare the array as a variant array
   Dim arRng() As Variant

'Declare the integer to store the number of rows
   Dim iRw As Integer

'Assign range to a the array variable
   arRng = Range("A1:A10")

'loop through the rows - 1 to 10
   For iRw = 1 To UBound(arRng)

'show the result in the immediate window
      Debug.Print arRng(iRw , 1)
   Next iRw 
End Sub

The UBound is used to set the array upper bound (eg 10) so that the loop knows to loop 10 times.

The Debug.Print function will show you the value contained in the array in the immediate window.

Assign value from multiple columns

Sub TestArrayValuesMultiple()
'Declare the array as a variant array
   Dim arRng() As Variant 

'Declare the integer to store the number of rows
   Dim iRw As Integer

'Declare the integer to store the number of columns
   Dim iCol as Integer

'Assign range to a the array variable
   arRng = Range("A1:C10")

'loop through the rows - 1 to 10
   For iRw = 1 To UBound(arRng,1)

'now - while in row 1, loop through the 3 columns
      For iCol = 1 to UBound(arRng,2)

'show the result in the immediate window
         Debug.Print arRng(iRw, iCol)
      Next iCol
   Next iRw 
End Sub

In the code above, we have populated the array with the values in Range(“A1:C10”).

The UBound is once again used – but this time it is needed twice – once to loop through the rows, and then again to loop through the columns.

The Debug.Print function will show you the value contained in the array in the immediate window.