Excel VBA – Convert Range To Array – 2 Dimentional

VBA Range to 2D Array

These list of topics are covered in this page.

1. Vba Code to initialize 2D Array with Range
2. Vba to write Array data to worksheet
3. Resize Array to fit worksheet.

This code converts worksheet data in specific range into an vba array. Lets see how it works.

1.Excel Macro – Convert Range To Array(2-Dimention)

Actually, it does not require any special function. It is almost easy like assigning a value to a variable.

Once you assign the range to the array, Excel automatically calculates & allocates array dimensions. You don’t have to specify the number of rows, columns or dimensions for the array.

Excel decides these parameters automatically & created a 2D Array by default. It will be clear from below example.

'--------------------------------------------------------------------------------
'Visit https://officetricks.com to get more Free & Fully Functional VBA Codes
'--------------------------------------------------------------------------------
Sub Range_To_Array()
    'Declare Array as variant & Range
    Dim rArray() As Variant, rRange As Range, iRow As Double, iCol As Double
    
    'Initialize Range to Reference a portion of Worksheet
    Set rRange = ThisWorkbook.Sheets("sheet1").Range("A1:D2")
    
    'Assign Range to Array
    rArray = rRange.Value
    
    'Read content of Array one by one
    For iRow = 1 To UBound(rArray, 1)
        For iCol = 1 To UBound(rArray, 2)
            Debug.Print rArray(iRow, iCol)
        Next
    Next           
End Sub

Once you convert the worksheet data to array, you get the advantage of using the Array function on the values. This way the code runs much faster than accessing worksheet data each time.

2. Convert Array to Range – Write to Worksheet

When we write values back to worksheet from array, you have to explicitly mention number of rows & columns the data should occupy.

For this first assign a cell to a range variable: set rng = Thisworkbook.Sheet(1).Range(“A1”) & then use range.resize as explained in this code.

Sub ArrayToRange()
    'Declare Array as variant & Range
    Dim rArray() As Variant, rRange As Range, iRow As Double, iCol As Double
    Set rRange = ThisWorkbook.Sheets("sheet1").Range("A1:D2")
    rArray = rRange.Value
    
           
    'Write values in Array back To a Range in worksheet
    Set rRange = ThisWorkbook.Sheets("sheet1").Range("F1:I2")
    rRange = rArray
    
    'OR Resize Range as per the structure of Array
    iRow = UBound(rArray, 1)
    iCol = UBound(rArray, 2)
    Set rRange = ThisWorkbook.Sheets("sheet1").Range("F1")
    Set rRange = rRange.Resize(iRow, iCol)
    MsgBox rRange.Address(0, 0)
    rRange = rArray
End Sub

To know how Range.resize works, read further in the next section.

3. Excel VBA – Resize Array to fit Worksheet

Once it is initialized, then it can be extended or resized as per the structure of the array.

'Get Number of Rows in Array
rows = UBound(arr,1)

'Get number of columns in Array
cols = UBound(arr,2)

'Resize array as per the array dimensions
set rng = rng.resize(rows,cols)

'Write Array value to the Range
rng = arr

So, the Excel VBA function Range.Resize holds the key here. It can be used to expand or compress the target destination range size.

I have often read that processing values from an array is much faster than processing values from Excel sheet. Though, I haven’t tested this. But, in case you want to move the values from worksheet or Table to an Array, then this code can be used.

External Reference: Here is another wonderful article from cpearson about arrays & worksheets.