Excel VBA: Copy an Array to a Worksheet Range Easily

To copy a two-dimensional VBA array to cells, assign it to a worksheet range whose size matches the array: Range("A1").Resize(UBound(data, 1), UBound(data, 2)).Value2 = data. The assignment writes the whole block at once, so you usually do not need a loop through individual cells.

How array-to-range assignment works

Excel’s Range object can receive a two-dimensional array of values through its Value or Value2 property. The first array dimension maps to worksheet rows; the second maps to columns. For example, a 3-by-2 array fills three rows and two columns. The top-left cell of the destination range receives the value at the array’s first row and first column.

Here is a complete example that creates a rectangular array and writes it starting at A1. It uses Resize to set the destination dimensions from the array bounds.

Save a copy of the workbook before running a macro: VBA changes cannot be undone with Ctrl+Z. This macro writes over A1:B3, so choose a destination that does not contain data you need to keep.

Sub WriteArrayToRange()
    Dim ws As Worksheet
    Dim data() As Variant
    Dim rowCount As Long
    Dim colCount As Long

    ' Select the destination worksheet.
    Set ws = ThisWorkbook.Worksheets("Report")

    ' Set the dimensions and create a two-dimensional array.
    rowCount = 3
    colCount = 2
    ReDim data(1 To rowCount, 1 To colCount)

    ' Fill each row and column.
    data(1, 1) = "Item"
    data(1, 2) = "Quantity"
    data(2, 1) = "Pens"
    data(2, 2) = 12
    data(3, 1) = "Notebooks"
    data(3, 2) = 5

    ' Write the complete array in one operation.
    ws.Range("A1").Resize(rowCount, colCount).Value2 = data
End Sub

Open the Visual Basic Editor from the Developer tab in Excel for Microsoft 365 on Windows, insert the code in a standard module, and run the procedure. Excel for Mac also supports VBA, although the editor shortcut can differ by keyboard; Option+F11, or Fn+Option+F11 on some keyboards, is commonly used. Excel for the web cannot run VBA macros.

Copy an existing worksheet block through an array

A common use is to read a source range into memory, optionally change its values, then write the array to another location. Assigning a range’s Value2 property to a Variant stores the range’s values as a two-dimensional array. The second assignment writes those values to a destination of the same row and column count.

In this example, with source data in A2:C10 on a worksheet named Data, the macro copies the values to E2:G10 on the same worksheet. The data is read before it is written, so the array holds a snapshot of the source values.

Sub CopyRangeThroughArray()
    Dim ws As Worksheet
    Dim data As Variant
    Dim sourceRange As Range

    ' Identify the worksheet and source block.
    Set ws = ThisWorkbook.Worksheets("Data")
    Set sourceRange = ws.Range("A2:C10")

    ' Load the source values into a two-dimensional array.
    data = sourceRange.Value2

    ' Resize the destination to the array's dimensions.
    ws.Range("E2").Resize(UBound(data, 1), UBound(data, 2)).Value2 = data
End Sub

This is a values copy, not a full worksheet copy. It does not carry over cell formatting, column widths, comments, validation, or formulas as formulas. Because Value2 reads calculated results, a formula cell in the source becomes its current result in the destination. To transfer formulas, use the appropriate formula property instead, and confirm that the formulas should refer to the destination’s new position.

Match the array dimensions to the destination

The destination should have exactly as many rows and columns as the array. If the array has R rows and C columns, write it to a range with R rows and C columns. A starting cell plus Resize is a convenient way to make that shape explicit:

UBound(data, 1) gives the upper bound of the first dimension, which is commonly the row count for an array created with lower bound 1.
UBound(data, 2) gives the upper bound of the second dimension, commonly the column count for that same kind of array.
Range("E2").Resize(rows, columns) describes the output block beginning at E2 without selecting cells.

For arrays that might have a lower bound other than 1, calculate the number of elements with UBound(data, dimension) - LBound(data, dimension) + 1. However, arrays passed around by other procedures can have different shapes and bounds, so verify that the array is two-dimensional and that the range you create is the intended size. Avoid relying on Excel to reconcile a mismatch: explicitly matching dimensions makes the result predictable and helps prevent unintended cells from being affected.

The destination’s top-left cell is the anchor. For instance, a four-row, three-column array assigned to a range resized from D5 fills D5:F8. The worksheet must have enough rows and columns available for the resized block.

One-dimensional arrays and single-cell results

A frequent source of errors is assuming every VBA array can be assigned directly to a range. A one-dimensional array, such as the result of Array("North", "South", "West"), is not the same shape as the two-dimensional array Excel returns for a multi-cell range. For a list, convert its elements into a two-dimensional array before writing, or write them individually if the list is very small.

This example turns a one-dimensional list into a vertical, two-dimensional array. It uses the list’s actual lower and upper bounds, then writes the result to A1:A3.

Sub WriteListVertically()
    Dim ws As Worksheet
    Dim items As Variant
    Dim output() As Variant
    Dim i As Long
    Dim itemCount As Long

    ' Set up the list and destination worksheet.
    Set ws = ThisWorkbook.Worksheets("Report")
    items = Array("North", "South", "West")
    itemCount = UBound(items) - LBound(items) + 1
    ReDim output(1 To itemCount, 1 To 1)

    ' Convert the one-dimensional list to a column array.
    For i = LBound(items) To UBound(items)
        output(i - LBound(items) + 1, 1) = items(i)
    Next i

    ' Write the column array in one operation.
    ws.Range("A1").Resize(itemCount, 1).Value2 = output
End Sub

A range containing exactly one cell is another special case: reading its value generally gives a scalar Variant rather than a two-dimensional array. You can write that scalar directly to a single cell. If your procedure must handle both one-cell and multi-cell input, account for the difference rather than applying UBound blindly; calling it on a scalar causes an error.

Choose Value2, Value, or Formula

For most array transfers involving ordinary values, Value2 is a straightforward choice. It avoids the Currency and Date data subtypes used by the Value property. Excel date values read through Value2 are numeric serial values; a destination cell’s number format determines whether a number displays as a date. If the appearance matters, apply a suitable number format separately.

Use Value when its Date or Currency subtype behavior is useful to your code, while remembering that the destination still needs appropriate formatting. Use a formula property when the goal is to place formulas, not their current calculated results. These properties do not copy the full formatting or other cell settings, so use a different copy method when those are part of the requirement.

Writing a two-dimensional array of values also does not create a dynamic-array formula. It fills the specified cell block with the array’s individual values. If you intend to use a worksheet formula that spills results, enter the formula through the relevant formula property and let Excel calculate the spill range instead.

Common problems and how to fix them

“Subscript out of range” while sizing the target: A one-dimensional array has no second dimension. Confirm the array shape or convert it to a two-dimensional array before using UBound(data, 2).
A type or assignment error: Check that the value being assigned is a rectangular, two-dimensional array and that the target is one contiguous range. Multi-area ranges are not a dependable target for a single array assignment.
Only one value appears: Check whether the source was actually a single cell, which returns a scalar, or whether the array was built with only one element. Inspect its dimensions before resizing the destination.
Values appear in the wrong orientation: Remember that dimension 1 is rows and dimension 2 is columns. To write across a row rather than down a column, build a two-dimensional array with one row and multiple columns.
Dates or numbers look different: The stored value and its display format are separate. Check the destination’s number format, especially when using Value2 with date serials.
Old cells remain after a shorter write: An array assignment fills its target block; it does not automatically clear cells outside that block. Clear an old output area deliberately if the new result is smaller, and make sure that area contains nothing that must be preserved.

When debugging, inspect the array’s dimensions and the destination address before the assignment. A breakpoint on the assignment line lets you examine variables in the Visual Basic Editor. For a macro that may overwrite data, test on a copy or an empty worksheet first.

Why write arrays in one operation?

For a large block, moving values between a worksheet range and a VBA array in a small number of operations is usually more efficient than reading or writing each cell in a VBA loop. Each interaction with the worksheet has overhead; a block assignment avoids repeating that interaction for every element. This is especially useful when a macro filters, rearranges, or calculates data in memory before returning a result to the sheet.

It is not automatically the best approach for every task. If you need to preserve cell-by-cell formatting, copy formulas with relative references, or apply different formats to individual cells, a value-array assignment alone does not meet that requirement. You may combine a block value write with separate formatting steps, or use a range copy when a true worksheet copy is needed. Choose based on what must be preserved, not only on the number of lines of VBA.

For very large arrays, memory use also matters: the array occupies memory in addition to the worksheet data. Keep only the data needed for the operation, and write results in sensible blocks if the full dataset would be too large for the available memory or the application’s practical limits.

Frequently Asked Questions

Can I assign an array directly to a range in VBA?
Yes. A rectangular, two-dimensional array can be assigned to a same-sized contiguous range through Range.Value or Range.Value2. A one-dimensional array needs reshaping for this kind of block assignment.

How do I copy an array starting at a particular cell?
Use the starting cell and resize it to the array dimensions, such as ws.Range("E2").Resize(rowCount, colCount).Value2 = data. The first array element goes into E2.

Can I copy formulas with an array?
A value-array transfer copies the formulas’ calculated results, not the formulas themselves. Use a formula property if the destination should contain formulas, or use a range-copy approach when you need a broader worksheet copy.

Does this work in Excel for the web?
No. Excel for the web can open workbooks containing VBA, but it cannot run or edit VBA macros. Use the Excel desktop application to run the code.

Can a macro’s array write be undone with Ctrl+Z?
No. Running a VBA macro clears Excel’s normal undo history, and its changes cannot be reversed with Ctrl+Z. Save a copy before running code that changes worksheet contents.

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.