Excel VBA Erase Array: What It Does and When to Use It

VBA & macros · Updated November 21, 2026

In Excel VBA, use Erase arrayName to reset an array: it releases the allocated storage of a dynamic array, but resets the elements of a fixed-size array to their default values. The distinction matters if you plan to reuse the array, and Erase does not clear cells on an Excel worksheet.

What Erase Does in VBA

Erase is a VBA statement for reinitializing an array. Its result depends on how the array was declared. A dynamic array is declared with empty parentheses and receives its dimensions later with ReDim. A fixed-size array has its bounds in its declaration. Erasing those two kinds of arrays has different effects.

  • Dynamic array: Erase releases the array’s allocated storage. The variable remains declared, but it no longer has allocated elements.
  • Fixed-size array: Erase resets every element to its type’s default value. The array keeps its declared bounds and can be used again without ReDim.

In neither case does the statement remove the variable declaration from the procedure or module. It changes the array’s contents or allocation state; it does not delete the VBA code or affect an identically named worksheet range.

Erasing and Reusing a Dynamic Array

A dynamic array is useful when its required size is not known until a macro runs. For example, a procedure might count matching records, allocate an array of that size, and fill it. Once the values are no longer needed, erasing the array releases its current allocation. To use it again, allocate it with ReDim.

Here is a small example. It allocates an array, assigns values, erases it, and then allocates it again with a different size:

Option Explicit

Sub ResetDynamicArray()
    Dim values() As Long

    ReDim values(1 To 3)
    values(1) = 12
    values(2) = 24
    values(3) = 36

    Erase values

    ' Allocate the array again before using its elements.
    ReDim values(1 To 2)
    values(1) = 5
    values(2) = 10
End Sub

After Erase values, do not assume that the earlier bounds or values are still available. In particular, trying to access an element before reallocating the dynamic array can cause a runtime error. Treat the array as unallocated until a later ReDim gives it bounds again. You can also use ReDim again after erasing without first assigning a new value to the array variable.

Erasing a Fixed-Size Array

A fixed-size array has its dimensions declared with the variable, such as Dim scores(1 To 3) As Double. Erasing it does not release its declared dimensions. Instead, VBA resets each element according to its data type. Numeric values become zero; variable-length strings become empty strings; variants become Empty; and object variables become Nothing. Fixed-length strings are filled with zero characters.

For instance, after the following statement, scores(1) evaluates to zero, but scores(1) remains a valid element because the array is still declared from 1 to 3:

Option Explicit

Sub ResetFixedArray()
    Dim scores(1 To 3) As Double

    scores(1) = 8.5
    scores(2) = 9.25
    scores(3) = 7.75

    Erase scores
    Debug.Print scores(1) ' Prints 0
End Sub

Use this behavior when the array’s size should stay constant but its old contents should not carry into the next calculation. If you need a different size, a fixed-size declaration is not the right tool; declare a dynamic array and size it with ReDim.

Choosing Between Erase, ReDim, and ReDim Preserve

These statements solve related but different problems. Choosing the right one helps prevent accidental loss of data or unnecessary resizing.

  • Erase arrayName resets a fixed-size array or releases a dynamic array’s allocation.
  • ReDim arrayName(bounds) sets or changes the bounds of a dynamic array. Without Preserve, resizing discards its existing contents.
  • ReDim Preserve arrayName(bounds) changes the size of a dynamic array while retaining existing values where possible. In VBA, Preserve can change only the last dimension of a multidimensional array.

For a dynamic array that needs to grow while retaining its existing entries, ReDim Preserve may be appropriate. Erasing first would defeat that purpose because the old values would be gone. For a fresh calculation where no old entries are needed, erasing and then allocating the required bounds can make the intended reset explicit.

When Erase Helps—and When It Is Unnecessary

Erasing can be useful when a procedure holds a large dynamic array that it will not use again for a while, or when a routine is about to start a new independent calculation. It can also help make the state of a reusable fixed-size array clear by resetting its elements. In a short procedure, however, a local array ordinarily goes out of scope when the procedure ends. Adding Erase automatically to every routine is not required for correctness.

With object arrays, erasing a fixed-size array resets its object references to Nothing; erasing a dynamic array releases the array allocation and its references. This does not destroy an object if other variables still refer to it. Object lifetime depends on whether references to that object remain elsewhere, not merely on the name of the array that once referred to it.

Before running any macro that changes workbook data, save a copy of the workbook. VBA macros cannot be undone with Ctrl+Z. The examples here operate on arrays in memory, but a larger macro may also change cells, files, or other application state.

Erase Is Not a Worksheet Clear Command

A common misunderstanding is that Erase clears an Excel range. It does not: it operates on a VBA array variable. If the goal is to remove values from cells, use a range method such as ClearContents. For example, with the target cells identified as A2:C20, the VBA statement Worksheets("Sheet1").Range("A2:C20").ClearContents clears cell contents while leaving the cells and their formatting in place.

That is different from clearing formats or deleting cells. Excel’s worksheet interface also offers commands such as Home > Clear > Clear Contents for clearing selected cell contents. The exact choice depends on whether you want to retain formatting, remove it, or shift cells; none of those worksheet operations is performed by Erase.

Common Errors and Misconceptions

  • Using an erased dynamic array immediately: After erasing, allocate the array again with ReDim before reading or writing an element.
  • Expecting a fixed array to become unallocated: Its bounds remain declared; only its elements are reset.
  • Expecting Erase to preserve values: It does not. Use ReDim Preserve when resizing a dynamic array while retaining values is the goal.
  • Confusing an array with a range: A range of cells and an array variable are different things, even if values have been copied between them.
  • Assuming a variable declaration is removed: Erase does not undo a Dim statement. It changes the state of the array associated with that variable.

If code fails when it checks an erased dynamic array’s bounds, review the order of operations. A bound-checking call such as LBound or UBound is useful only when the dynamic array has been allocated. Track the allocation state in your procedure, or structure the code so that bounds are checked only after ReDim.

Excel Version and Platform Notes

The behavior of the VBA Erase statement is part of VBA array handling, not a worksheet function, so it is not entered into a cell. In Excel for Microsoft 365 on Windows, write it in a VBA procedure in the Visual Basic Editor. Desktop Excel for Mac also supports VBA, although some Excel object-model features and operating-system integrations differ between Mac and Windows. Excel for the web does not run VBA macros; use desktop Excel to execute this code.

Frequently Asked Questions

Does Erase delete a VBA array?

It does not delete the variable declaration. For a dynamic array, it releases the allocated array storage, leaving the variable available for a later ReDim. For a fixed-size array, it resets the elements while keeping the declared array bounds.

Can I use a dynamic array after Erase?

Yes, but allocate it again before using its elements. For example, after Erase values, use a statement such as ReDim values(1 To 10) to give the dynamic array bounds and storage.

Does Erase set every array value to zero?

Only numeric elements reset to zero. Other types have their own default state: strings are empty (or zero-filled for fixed-length strings), variants are Empty, and object references are Nothing. For a dynamic array, Erase releases its allocation rather than leaving a set of reset elements ready to read.

Is Erase the same as ReDim?

No. Erase resets a fixed array or releases a dynamic array’s allocation. ReDim allocates or resizes a dynamic array. After erasing a dynamic array, use ReDim if you need to use it again.

How do I erase values from Excel cells with VBA?

Use a worksheet range method, not the VBA array statement. For example, call ClearContents on the range whose values should be removed. It clears contents without removing the cell formatting; choose a different worksheet command if formatting or cell positions should change too.

Related

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.