VBA Range Array: Read and Write Cells Efficiently

A VBA range array is a Variant array that holds values from a worksheet range in memory, so your macro can process many cells without reading them one by one. Assign a multi-cell range’s Value2 property to a Variant, edit the resulting two-dimensional array, then assign it back to a same-sized range.

What a VBA range array means

A worksheet range is an Excel object; an array is a group of values held in VBA memory. They are related, but they are not the same thing. When VBA assigns a rectangular range containing multiple cells to a Variant variable, Excel returns the cell values as a two-dimensional array. The first dimension represents worksheet rows, and the second represents columns.

For example, a range covering A2:C4 has three rows and three columns. After the values are loaded into an array, its elements can be addressed by row and column indexes. This makes it practical to search, calculate, clean, or reorganize data in memory, then send the results to a worksheet in one assignment.

This approach is often called bulk reading and writing. It can be much faster than repeatedly calling a worksheet cell inside a loop, especially when a macro handles thousands of cells, because it reduces repeated communication between VBA and Excel.

Read a range into an array

Use a Variant variable to receive a range’s values. For a range with multiple cells, the result is normally a two-dimensional array, even when the range has only one row or one column. The following example reads A2:C4 and displays the value from the range’s top-left cell, A2.

Sub ReadRangeIntoArray()
    Dim ws As Worksheet
    Dim data As Variant

    Set ws = ThisWorkbook.Worksheets("Sheet1")
    data = ws.Range("A2:C4").Value2

    ' The first index is the row; the second is the column.
    Debug.Print data(1, 1)
End Sub

The array indexes here are relative to the selected range, not the worksheet. Thus data(1, 1) refers to A2, while data(2, 3) refers to C3. For a typical range returned by Excel, the first element in each dimension has a lower bound of 1. Use LBound and UBound when you want code that explicitly checks the available bounds.

Loop through rows and columns

Nested loops let a macro visit each array element. The outer loop below moves through rows and the inner loop through columns. Its bounds come from the array itself, so the loop adapts if the selected range changes size.

Sub InspectRangeArray()
    Dim ws As Worksheet
    Dim data As Variant
    Dim rowIndex As Long
    Dim columnIndex As Long

    Set ws = ThisWorkbook.Worksheets("Sheet1")
    data = ws.Range("A2:C4").Value2

    For rowIndex = LBound(data, 1) To UBound(data, 1)
        For columnIndex = LBound(data, 2) To UBound(data, 2)
            Debug.Print rowIndex, columnIndex, data(rowIndex, columnIndex)
        Next columnIndex
    Next rowIndex
End Sub

The second argument to LBound or UBound identifies which dimension to check: 1 for rows and 2 for columns in this example. A loop over a one-column range still uses both indexes, such as data(rowIndex, 1); it is not automatically a one-dimensional array.

Change values and write the array back

After changing elements in memory, assign the array to a destination range with matching dimensions. In this example, B2:B5 contains 10, 20, 30, and 40. The macro doubles each value and writes 20, 40, 60, and 80 to C2:C5. The source and destination ranges each have four rows and one column.

Before running a macro, save a copy of the workbook: VBA changes cannot be undone with Ctrl+Z. This example writes to a separate output column, but the same precaution applies to macros that overwrite their source data.

Sub DoubleValuesInColumn()
    Dim ws As Worksheet
    Dim data As Variant
    Dim rowIndex As Long

    Set ws = ThisWorkbook.Worksheets("Sheet1")
    data = ws.Range("B2:B5").Value2

    For rowIndex = LBound(data, 1) To UBound(data, 1)
        If Not IsError(data(rowIndex, 1)) Then
            If IsNumeric(data(rowIndex, 1)) Then
                data(rowIndex, 1) = CDbl(data(rowIndex, 1)) * 2
            End If
        End If
    Next rowIndex

    ws.Range("C2:C5").Value2 = data
End Sub

The checks avoid trying to convert an Excel error value into a number and leave nonnumeric entries unchanged. Depending on the task, you may instead want to skip blank cells explicitly, replace errors, or report unexpected text. Choose that behavior deliberately; a blanket conversion can alter data that should be preserved.

Value, Value2, and formulas

Value2 transfers the underlying values without using the VBA Date and Currency subtypes that can be returned through Value. Excel dates are stored as serial numbers, so a date read through Value2 is generally represented as a number. If you need to work with dates as VBA dates, account for that representation and the workbook’s date system rather than assuming the value is already a Date.

Use Value or Value2 when you want cell results, such as numbers, text, dates, or the displayed result of a formula. Use Formula when you need to read or write formulas themselves. For example, assigning a formula-containing range’s Value2 to an array captures calculated results, not formula text. Writing that array back writes values and replaces any formulas in the destination cells.

Formatting is separate from the values. Assigning an array to Value2 does not copy number formats, font settings, borders, or column widths. If the output needs particular formatting, apply it separately or use an appropriate copy operation.

Single cells, one-dimensional ranges, and multiple areas

One common source of errors is assuming every range produces the same array shape. A range containing just one cell returns a scalar value when assigned to a Variant, not a two-dimensional array. Code that then tries to access data(1, 1) can fail because data is not an array in that case.

A range with several cells in one row or one column still returns a two-dimensional array. For instance, A1:D1 has one row and four columns, while A1:A4 has four rows and one column. The unused dimension has a size of one. A robust procedure that accepts arbitrary range sizes should handle the single-cell case separately or ensure the input always contains at least two cells.

Discontiguous, or multi-area, ranges are another special case. A single rectangular array cannot represent separated blocks as one ordinary rectangle without gaps or an explicit mapping. Process each area separately, or first copy the data into a contiguous range if that suits the task.

Choosing a range and matching the output

A fixed address such as A2:C100 is straightforward when the dataset has a known size. If rows are added over time, determine the last populated row before reading the range, and guard against a sheet with no data rows. The best method depends on the sheet’s layout: a reliable key column, an Excel table, or a defined named range can each provide a useful boundary.

Keep the output range aligned with the array’s shape. If the array has four rows and three columns, assign it to a four-by-three destination. A mismatched destination can produce an error or unexpected results, so calculate the destination from the array dimensions when the size varies. Also check that the output will not overwrite headers, formulas, or other data that must remain intact.

Excel tables can be useful when data grows because their data region expands as rows are added. VBA can read a table’s data body range, but a table with no data rows needs separate handling because its data body range may not be available as expected. Avoid assuming that a fixed worksheet address will always include new records.

Why arrays can improve macro speed

Every interaction with a worksheet object has overhead. A macro that reads one cell, changes it, and writes it back during each iteration repeatedly crosses between the worksheet and VBA. Bulk processing instead reads a block once, performs the loop on values held in memory, and writes the block once. For large blocks, that pattern is commonly more efficient and easier to reason about.

Arrays are not automatically the best choice for every operation. A few cells may not justify extra code, and Excel’s built-in worksheet features may already perform a calculation or transformation more clearly. Very large arrays also use memory, and a complicated macro can become harder to maintain if a simple direct approach would suffice. The useful rule is to consider bulk arrays when code repeatedly processes a substantial rectangular block.

Common errors and practical fixes

Subscript out of range: The requested index may exceed an array’s bounds, or the code may have confused worksheet row numbers with range-relative array indexes. Check the bounds with LBound and UBound.
Type mismatch: An element may contain an Excel error, text, or another value that cannot be used in the intended calculation. Test the value before converting or calculating, and decide how the macro should handle unexpected content.
Array or range dimensions do not match: Confirm the source array’s row and column counts and make the destination the same size.
Single-cell failure: A one-cell range may return a scalar rather than an array. Add a separate branch for one cell or make the input range consistently larger.
Formulas disappeared: Writing values back through Value2 replaces destination formulas with those values. Use the formula property when formulas, rather than calculated results, are what you intend to preserve or write.
Dates look like numbers: Value2 returns date serial values. Apply a date format to the output if needed, and convert values deliberately if VBA date arithmetic is required.

When to use an array instead of other approaches

Use a VBA range array when a macro needs to inspect or transform many cell values with custom logic, especially when the same operation can be applied in memory and written back as a block. Use ordinary range operations when the task is a small, direct edit. Use worksheet formulas when results should update automatically as source cells change, and consider Excel’s built-in sorting, filtering, or table features when they already meet the need.

VBA macros run in desktop Excel, including Excel for Microsoft 365 on Windows. Excel for the web can open a workbook containing VBA but does not run VBA macros; use desktop Excel for these procedures. VBA is also available in desktop Excel for Mac, although some Excel features and automation capabilities differ from Windows. The examples use basic worksheet and range operations, but platform-specific behavior should be checked if a larger macro relies on Windows-only components.

Frequently Asked Questions

How do I put a VBA range into an array?
Declare a Variant variable and assign a multi-cell range’s Value2 property to it, such as data = Worksheets("Sheet1").Range("A2:C10").Value2. The result is a two-dimensional array indexed by range-relative row and column.

How do I write an array to a range in VBA?
Assign the array to the destination range’s Value2 property. The destination should have the same number of rows and columns as the array. This writes the array’s values, not its formatting.

Why does a one-cell range not behave like an array?
When a single cell’s value is assigned to a Variant, VBA receives a scalar value rather than a two-dimensional array. Handle the one-cell case separately if your procedure can receive ranges of different sizes.

Are VBA range arrays zero-based?
Arrays returned from ordinary multi-cell Excel ranges typically start at 1 in both dimensions. Use LBound and UBound to verify bounds rather than assuming indexes, particularly when working with arrays created by other code.

Does assigning an array back preserve formulas and formatting?
No. Writing an array through Value or Value2 writes cell values and can replace formulas in the destination; it does not copy formatting. Use the formula property for formula text and handle formatting separately.

Try it: the Column Letter to Number: aA is 27, XFD is 16,384. Converts both ways.

More in VBA & macros

Sources: Function behaviour and menu paths: Microsoft Support documentation for Excel. Method & sources

Spotted an error in this guide? Let us know.