Excel VBA Clear Array Contents: ReDim, Erase, and More

VBA & macros · Updated November 21, 2026

In Excel VBA, use Erase arrayName to clear an array: it deallocates a dynamic array, but resets a fixed-size array’s elements to their default values while keeping its dimensions. If you need to keep the array allocated and preserve its bounds, set its elements to suitable empty or default values in a loop instead.

What “clear an array” means in VBA

There are two different results people may mean by “clear.” One is removing the array’s current data while leaving a usable array with the same bounds. The other is releasing a dynamic array’s allocated storage so it is no longer dimensioned. VBA’s Erase statement behaves differently for those cases, so first determine whether the declaration is fixed-size or dynamic.

A fixed-size array has bounds in its declaration, as in Dim scores(1 To 5) As Long. Its size is set when the procedure or module is compiled. A dynamic array is declared with empty parentheses, such as Dim scores() As Long, and given bounds later with ReDim. The distinction matters because Erase does not make a fixed-size array disappear, while it does deallocate a dynamic one.

These are in-memory VBA arrays, not worksheet ranges. Erase does not remove cell contents, clear a table, or empty a Collection. For worksheet cells, use a range method such as Range("A1:C10").ClearContents; that is a separate operation and should be chosen deliberately.

Use Erase with a dynamic array

For a dynamic array, Erase releases the memory used by its elements and leaves the array unallocated. You can allocate it again later with ReDim. This is usually the clearest way to discard a dynamic array completely before starting a new operation.

Before running a macro, save a copy of the workbook. VBA macros cannot be undone with Ctrl+Z. The following procedure illustrates erasing and then allocating the array again:

Sub ResetDynamicArray()
    Dim values() As Long

    ' Allocate the array and put a value in it.
    ReDim values(1 To 4)
    values(1) = 10

    ' Deallocate the dynamic array.
    Erase values

    ' Allocate it again for later use.
    ReDim values(1 To 4)
End Sub

After Erase values, do not assume the original bounds are still available. Allocate the array with ReDim before reading or writing its elements. Calling UBound on an unallocated dynamic array raises an error, which is a common source of “Subscript out of range” failures in code that tries to inspect an array immediately after erasing it.

Use Erase with a fixed-size array

With a fixed-size array, Erase resets its elements but does not remove its declared bounds or make it dynamic. For example, after erasing a fixed array declared from 1 to 3, those indices remain available and can be read or assigned again. The values are reset to defaults appropriate to their data types.

For a numeric array, the default is zero. A Boolean becomes False; a variable-length String becomes a zero-length string; a Variant element becomes Empty; and an object reference becomes Nothing. Date values reset to their zero value. User-defined types are reset through the defaults of their members. Fixed-length strings have fixed storage and can contain null characters after reset, so they do not behave exactly like ordinary variable-length strings.

Sub ResetFixedArray()
    Dim values(1 To 3) As Long

    ' Demonstrate a value that will be reset.
    values(1) = 10

    ' Reset elements; the bounds remain 1 through 3.
    Erase values
End Sub

Because the array remains dimensioned, the same fixed-size array cannot be resized with ReDim. If the required number of elements can change, declare a dynamic array instead.

Clear values but keep the array allocated

If later code needs the existing bounds, or you want to reuse a dynamic array without deallocating it, loop through the elements and assign a reset value. The assignment must be compatible with the element type. For a numeric array, assigning zero is straightforward:

Sub ClearNumericValuesKeepBounds()
    Dim values(1 To 3) As Long
    Dim i As Long

    values(1) = 10

    ' Keep the array dimensions and clear each value.
    For i = LBound(values) To UBound(values)
        values(i) = 0
    Next i
End Sub

LBound and UBound make the loop work across the declared range instead of relying on a guessed starting index or length. This is useful when the lower bound is not 1. VBA’s Option Base can affect the default lower bound for some declarations, but explicit bounds and LBound are clearer when bounds matter.

For a Variant array, assign Empty to each element if that is the intended reset state. For an array of object references, clear each reference with Set:

Sub ClearObjectReferences()
    Dim items(1 To 3) As Object
    Dim i As Long

    ' Release any object reference held by each element.
    For i = LBound(items) To UBound(items)
        Set items(i) = Nothing
    Next i
End Sub

Setting an object element to Nothing releases that reference; it does not necessarily destroy the object if another variable still refers to it. For arrays of custom types, clear the relevant fields individually when a type’s default values are not the values your application needs.

Multidimensional arrays and ReDim Preserve

Erase applies to the whole array, including all dimensions. If you want to keep a two-dimensional array’s shape but clear its entries, use nested loops and assign the appropriate default value to every element. For example, use one loop for rows and another for columns, with each loop running from the corresponding LBound to UBound. This avoids assuming that either dimension starts at 1.

ReDim Preserve is not a general-purpose clear operation. It retains existing values while changing the size of a dynamic array, and in VBA it can change only the upper bound of the last dimension. It cannot change the lower bound, and it does not clear all values. If the goal is to discard all values and start fresh, use Erase followed by ReDim. If the goal is to retain the bounds, loop through and overwrite the elements.

Without Preserve, a new ReDim allocation discards existing contents. That can be useful when starting a new dataset, but it is destructive to the current values. Make the choice explicit rather than adding or removing Preserve as a workaround for an array error.

Choosing the right method

Use the method that matches what the next part of the macro expects. A cleared array may be empty in the everyday sense but still allocated, or it may have no allocated bounds at all. Those states are not interchangeable in VBA.

  • Release a dynamic array and its storage: use Erase arrayName. Use ReDim before accessing it again.
  • Reset a fixed-size array: use Erase arrayName. The declared bounds remain, and elements return to type defaults.
  • Keep the current dimensions and choose the reset value: loop over the bounds and assign each element a compatible value.
  • Resize a dynamic array while retaining existing data: use ReDim Preserve only where its last-dimension restriction suits the array.
  • Clear worksheet cells: use an appropriate Range method, not Erase.

Consider a macro that reads a variable number of records into a dynamic array. At the end of one run, erasing the array is a sensible way to discard that run’s data. If the next procedure expects an array with the same set of indices, however, it must allocate the array again or receive a still-allocated array whose elements have been reset. Documenting that expectation can prevent later procedures from receiving an unallocated array unexpectedly.

Common errors and misconceptions

“Erase always makes an array unallocated.” It does that for dynamic arrays, not fixed-size arrays. For fixed-size arrays it resets the contents, leaving the dimensions intact.

“I can assign an empty string to any array.” An array is not a single string. Assigning "" to a typed numeric array is not a valid way to clear its elements and can cause a type mismatch. Set each element to a value suitable for its declared type, or use Erase.

“IsArray tells me whether a dynamic array has been allocated.” IsArray identifies whether a value is an array; it is not a reliable test that a dynamic array currently has usable bounds. Likewise, UBound is not a safe allocation test by itself because it errors on an unallocated dynamic array. In code that controls allocation, a Boolean flag set when the array is dimensioned can make the state explicit. Alternatively, handle the specific error deliberately rather than hiding errors with a broad On Error Resume Next.

“Set arrayName = Nothing clears an array.” Set ... = Nothing is for object variables, not ordinary array variables. It is appropriate for an individual element declared to hold an object reference, not as a general array-clearing command.

Another source of confusion is arrays stored in Variants or returned from functions. Their exact assignment behavior depends on how the Variant or function result is declared and used. When working with such code, identify whether the variable itself is a Variant holding an array or a typed array, and choose an operation supported by that declaration. Do not assume that assigning a scalar empty value is equivalent to erasing every kind of array.

Frequently Asked Questions

Does Erase reset a dynamic array to zero?
Not while leaving it allocated. Erase deallocates a dynamic array. ReDim it before use; the newly allocated numeric elements have their normal default values, such as zero.

Can I clear an array without changing its size?
Yes. Loop from each dimension’s lower bound to its upper bound and assign each element the appropriate reset value. For numeric elements, that is commonly zero; object elements need Set element = Nothing.

Can I use ReDim after Erase?
Yes, for a dynamic array. Use ReDim to allocate it again and specify the bounds and element type as appropriate. A fixed-size array cannot be resized with ReDim.

Does Erase work on a two-dimensional array?
Yes. It erases the array as a whole. To keep the two dimensions and clear only the values, use nested loops over both dimensions instead.

Does Erase clear cells in an Excel worksheet?
No. It works on VBA array variables in memory. To remove worksheet cell values, use a Range operation such as ClearContents on the intended cells.

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.