Excel VBA: Copy an Entire Worksheet into an Array Safely

In Excel VBA, assign a worksheet range’s Value2 property to a Variant to copy its cell values into a two-dimensional array. For a practical “whole sheet” copy, use the worksheet’s populated range or a clearly defined data range; the entire Excel grid is far too large to load into memory usefully.

What “entire sheet” means in VBA

A worksheet contains up to 1,048,576 rows and 16,384 columns: more than 17 billion cells. An array containing every cell, including blanks, would be an impractical memory request for ordinary Excel automation. Most people asking to copy an entire sheet to an array mean the values in the area that contains their data, not every possible cell in the grid.

There are a few different boundaries you might mean:

A known data block: for example, A1:F500. This is the clearest and most reliable option when the layout is fixed.
The used range: the area Excel currently regards as used, returned by Worksheet.UsedRange. It may include formatted or previously edited cells beyond the current data.
All populated cells: a boundary you determine from actual values or formulas. This can be useful when the data changes size, but needs a deliberate rule for what counts as populated.

An array is not a second worksheet. It holds data in VBA memory, so it does not automatically retain cell formatting, column widths, comments, charts, validation rules, or other worksheet features.

Copy a range into a two-dimensional array

For a known rectangular range, assign the range’s Value2 property directly to a Variant. The result is normally a two-dimensional array: the first dimension represents rows and the second represents columns. This compact approach is much faster than reading each cell separately when a range contains many cells.

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

    ' Read the known data block into memory.
    Set ws = ThisWorkbook.Worksheets("Data")
    data = ws.Range("A1:F500").Value2

    ' Example: display the value from worksheet cell B2.
    Debug.Print data(2, 2)
End Sub

In this example, the range begins at A1, so array element data(1, 1) corresponds to A1 and data(2, 2) corresponds to B2. The array indices refer to positions within the selected range; they are not necessarily the worksheet’s row and column numbers. If the selected range starts at C5, its first cell is still normally addressed as data(1, 1).

There is an important exception: when the range has only one cell, VBA returns a single value rather than a two-dimensional array. Code that must handle both one-cell and multi-cell ranges should account for this difference rather than assuming every result can be indexed twice.

Read the worksheet’s UsedRange

When the data area is not a fixed size, UsedRange is a convenient starting point. It returns a Range object for the worksheet’s used area, and you can read that area with Value2. The following example handles the one-cell case as well as larger ranges and reports the array’s dimensions.

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

    ' Select the worksheet whose data you want to read.
    Set ws = ThisWorkbook.Worksheets("Data")
    Set sourceRange = ws.UsedRange

    ' A one-cell range returns a scalar, so wrap it as a 2-D array.
    If sourceRange.Cells.CountLarge = 1 Then
        ReDim data(1 To 1, 1 To 1)
        data(1, 1) = sourceRange.Value2
    Else
        data = sourceRange.Value2
    End If

    Debug.Print "Rows: "; UBound(data, 1)
    Debug.Print "Columns: "; UBound(data, 2)
End Sub

Before running any macro, save a copy of the workbook. VBA macros cannot be undone with Ctrl+Z. This example reads data and prints dimensions in the Immediate window; it does not change the worksheet. In the Visual Basic Editor, show the Immediate window with Ctrl+G on Windows. On Mac, the Visual Basic Editor is available in Excel, but its menus and shortcuts can differ by version; use the editor’s View menu if the shortcut is unavailable.

Check the UsedRange boundary before relying on it

Excel’s used area can be larger than the visible table. For example, a user might format an entire column, type data far down the sheet and delete it, or apply formatting to cells beyond the real dataset. As a result, UsedRange can include many blank cells. An array read from that range will contain corresponding empty elements, which can increase memory use and processing time.

If you know the data starts at A1 and has headers in row 1, a common approach is to find the last row and column that matter, then read just that rectangle. The definition of “matter” is application-specific: a report might include formulas that currently display empty strings, while another task might care only about nonblank displayed values. Decide whether formulas, formatting, or values define your boundary before writing the detection logic.

For a regularly maintained table, consider converting the data to an Excel Table and using its DataBodyRange to refer to the body rows. A table gives the data a defined structure as rows are added, although an empty table has no data body. Always check for that case before trying to read its body into an array.

What Value2 copies—and what it does not

Value2 copies cell results as VBA values, not worksheet presentation. Text remains text, numeric values remain numbers, and empty cells are represented as empty values. If a cell contains a formula, reading Value2 gives its calculated result rather than the formula text. Error cells are returned as error values, so code that processes every item should be prepared for errors instead of assuming every element is ordinary text or a number.

Dates are another detail worth knowing. Excel stores dates as serial numbers, and Value2 returns the underlying numeric value rather than converting it to VBA’s Date subtype. Cell number formatting is not part of the array, so a serial value may look like an unformatted number when printed or written somewhere else. To display it as a date later, you need to format the destination cell or convert the value appropriately in VBA.

To copy formulas rather than their calculated results, read the range’s Formula property instead. That gives formula expressions for formula cells, but it still does not copy formats or other sheet features. Choose between Value2 and Formula based on what the next step needs: stable results for analysis, or formulas that should remain formulas when transferred.

Loop through the array and write it back

Once the range is in memory, process its elements in VBA without repeatedly communicating with worksheet cells. For example, a loop can inspect each row, total a numeric column, or filter records before writing results. For a large dataset, doing the calculation in memory and writing a result range once is generally more efficient than reading and writing cells one at a time.

You can write a two-dimensional array back to a worksheet by assigning it to a destination range of matching dimensions. If the array has 500 rows and 6 columns, the destination needs 500 rows and 6 columns. For an array read from a range beginning at A1, the following pattern writes the same values starting at H1:

Sub WriteArrayToSheet()
    Dim ws As Worksheet
    Dim data As Variant
    Dim outputRange As Range

    ' Read a multi-cell block into a 2-D array.
    Set ws = ThisWorkbook.Worksheets("Data")
    data = ws.Range("A1:F500").Value2

    ' Match the destination dimensions to the source array.
    Set outputRange = ws.Range("H1").Resize( _
        UBound(data, 1), UBound(data, 2))
    outputRange.Value2 = data
End Sub

This copies values, not formulas, because the source was read through Value2. It also overwrites the destination cells, so check that the output area is clear and that it does not overlap data you need to keep. When the array contains only one cell, handle that case separately because its value may not have array bounds.

Array copying versus copying a worksheet

Use an array when the goal is to calculate, inspect, filter, or transform cell data in VBA. It is a good fit for moving values between a worksheet and memory, especially when many cells are involved. It is not the right tool when the task is to duplicate the worksheet itself.

To make a worksheet-level copy, use the worksheet’s Copy method, such as Worksheets("Data").Copy. That operation creates or places a worksheet copy and preserves worksheet features that a values array cannot represent. It does not turn the sheet into an array. Conversely, assigning an array to a range transfers array contents but does not reproduce sheet formatting, charts, or layout.

For a small range, a direct range-to-range assignment may be simpler than creating an array. For example, assigning one range’s Value2 to another transfers values without requiring explicit array loops. For a full worksheet clone, use a worksheet copy; for data processing, use a bounded range and an array. The right method depends on whether the thing being copied is cell data or the worksheet object.

Common errors and practical safeguards

“Subscript out of range”: an array index may be wrong, or the worksheet name may not exist in the workbook referenced by ThisWorkbook. Check the sheet name and remember that array positions usually start at 1 for range assignments.
Type mismatch: the code may be treating a one-cell result as a two-dimensional array, or a cell may contain an error value. Handle single cells separately and test for errors before doing numeric operations.
Unexpectedly large array: inspect UsedRange for leftover formatting or distant edited cells. If the dataset has a known boundary, use it instead of loading unnecessary blank cells.
Wrong output shape: the array dimensions and destination range dimensions must match. Use UBound to size the destination rather than hard-coding a different number of rows or columns.
Missing appearance or formulas: Value2 transfers results only. Read Formula when formula expressions are needed, and use range or worksheet copy operations when formatting or sheet features must be retained.

Also consider merged cells and hidden rows or columns. A range read into an array follows the selected rectangular range, not the visual appearance of the sheet. Hidden rows remain part of that range, and merged cells can make the values less intuitive because a merged area’s content is stored in its upper-left cell. If the layout has merged cells, test the exact range and decide whether the data should be normalized before processing.

Frequently Asked Questions

Can I copy every cell in a worksheet into a VBA array?
The worksheet grid has more than 17 billion cells, so loading every cell is not practical for normal VBA work. Copy the actual data region or a defined range instead.

Does assigning Range.Value2 to an array copy formulas?
No. It returns calculated results for formula cells. Read the range’s Formula property if you need formula expressions in the array.

Why does my array have different bounds than the worksheet range?
Array indices are positions within the selected range. The first cell of a multi-cell range is normally (1, 1), even when that range begins at a worksheet address such as C5.

Does UsedRange include blank cells?
It can include cells that were formatted or edited even if they currently look blank. Check the returned range’s address and dimensions, and use a more precise boundary when extra cells would cause problems.

How do I copy the array back to Excel?
Assign the array to a destination range with the same row and column counts. A destination range can be sized with Resize and the array’s UBound values.

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.