[Excel VBA] How to Batch Write 2D Array Values to a Cell Range for High Speed

There are many situations where large amounts of processing results calculated or processed in VBA macros need to be written to a worksheet. If you use a For loop to write values one cell at a time, the processing speed becomes extremely slow as the amount of data increases.

In contrast to reading “Cell Range -> Array,” this article explains the fastest and most efficient way to instantly write data stored in a VBA 2D array to a specified cell range.

The Core: The .Resize Property

The key to this technique is the .Resize property of the Range object. .Resize has the function of resizing (expanding or shrinking) a reference cell range to a specified number of rows and columns.

Syntax: BaseCell.Resize(RowSize, ColumnSize)

For example, Range("A1").Resize(10, 5) returns a range of “10 rows x 5 columns” based on cell A1, which corresponds to the range A1:E10.

By combining .Resize with the UBound function (which retrieves the size of an array), you can make the destination cell range size match the array size perfectly.

Complete VBA Code

The following code creates a 1000-row x 5-column 2D array in VBA memory and writes all the data to the sheet at once, starting from cell A1.

Sub WriteArrayToRange()

    ' Declare variables
    Dim dataArray(1 To 1000, 1 To 5) As String
    Dim r As Long
    Dim c As Long
    Dim outputCell As Range
    
    '--- 1. Create data in a 2D array in VBA memory ---
    ' (Assuming the array is created as a result of other processing)
    For r = 1 To UBound(dataArray, 1)
        For c = 1 To UBound(dataArray, 2)
            dataArray(r, c) = "Row" & r & ", Col" & c
        Next c
    Next r
    
    '--- 2. Set the starting cell for writing ---
    Set outputCell = ThisWorkbook.Worksheets("Sheet1").Range("A1")
    
    '--- 3. Resize the range to match the array and assign values at once ---
    ' This single line writes all data at high speed
    outputCell.Resize(UBound(dataArray, 1), UBound(dataArray, 2)).Value = dataArray
    
    MsgBox "Array data has been written to the sheet in a batch."

End Sub

Running this code reflects data for 1000 rows x 5 columns (5000 cells total) onto the sheet instantly, far faster than writing one cell at a time in a loop.

Code Explanation

1. Preparation of the Array

Dim dataArray(1 To 1000, 1 To 5) As String

First, prepare a 2D array in VBA memory to hold the data you want to write to the sheet. It is common to define the array index to start from 1, similar to how Range.Value operates.

2. Resize and Batch Assignment

outputCell.Resize(UBound(dataArray, 1), UBound(dataArray, 2)).Value = dataArray

This single line is the core of the speed optimization.

• UBound(dataArray, 1): Retrieves the number of elements in the first dimension of the array (row count).
• UBound(dataArray, 2): Retrieves the number of elements in the second dimension of the array (column count).
• outputCell.Resize(...): Dynamically creates a cell range with the exact same number of rows and columns as the array, starting from outputCell (A1).
• .Value = dataArray: Directly assigns the array variable to the .Value property of the created range. This ensures that communication between VBA and Excel happens only once, transferring all data at high speed.

Summary

The fastest procedure for writing VBA processing results to a worksheet is as follows:

1. Fully prepare the data to be written as a 2D array in VBA memory.
2. Use the UBound function to get the row and column counts of that array.
3. Use the .Resize property on the starting cell to create a range of the exact same size as the array.
4. Batch assign the array variable to the .Value property of the created range.

This approach of “completing processing in memory and writing to the sheet only once at the end” is the most important basic principle for maximizing VBA performance.