excelavon

VBA Array Clear: How to Erase, Reset, and Reuse Arrays

VBA & macros · Updated November 3, 2026

VBA Array Clear: How to Erase, Reset, and Reuse Arrays

To clear a VBA array, use Erase arrayName. For a dynamic array, Erase releases its allocated storage; for a fixed-size array, it resets each element to its type’s default value, so the right approach depends on whether you want to discard the array or just clear its contents.

What “clear an array” means in VBA

VBA does not provide an array method named Clear. The Erase statement is the built-in operation, but its effect differs according to how the array was declared. A dynamic array has no bounds until you allocate it with ReDim. A fixed-size array has bounds in its declaration and keeps those bounds after its elements are erased.

This distinction matters when reusing an array. If a dynamic array is erased, it is unallocated: you must use ReDim before accessing elements again. If a fixed-size array is erased, it remains the same size and can be filled again immediately. In either case, Erase changes the array’s data or allocation; it does not delete the array variable from your procedure.

Clear a dynamic array with Erase

Declare a dynamic array with empty parentheses, allocate it with ReDim, and erase it when you no longer need its allocated elements. This example uses a dynamic array of Long values:

Save a copy of the workbook before running a macro. VBA macros cannot be undone with Ctrl+Z.

Sub ClearDynamicArray()
    Dim values() As Long

    ' Allocate three elements and give them sample values.
    ReDim values(1 To 3)
    values(1) = 12
    values(2) = 24
    values(3) = 36

    ' Release the dynamic array's allocated storage.
    Erase values

    ' Allocate it again before using an element.
    ReDim values(1 To 3)
    values(1) = 48
End Sub

After Erase values, the array is not a three-element array containing zeros; it is an unallocated dynamic array. Trying to read values(1) or ask for its bounds before allocating it again can cause a runtime error. If you need the array again, use ReDim to give it bounds and storage.

Clear a fixed-size array

A fixed-size array gets its bounds in the declaration. Erasing one resets its elements but does not remove its size. For numeric elements, the reset value is zero; for variable-length strings it is an empty string; for object references it is Nothing. Variant elements become Empty. The exact default therefore depends on the element type.

For example, the following fixed-size array still has three elements after Erase. It does not need ReDim before reuse:

Save a copy of the workbook before running a macro. VBA macros cannot be undone with Ctrl+Z.

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

    ' Set sample values in the fixed-size array.
    scores(1) = 80
    scores(2) = 90
    scores(3) = 100

    ' Reset each element; the declared bounds remain.
    Erase scores

    ' The array can be assigned to again without ReDim.
    scores(1) = 75
End Sub

Use this behavior when the array’s size is fixed and you want its elements reset for another pass through a procedure. If you need to change the size, declare a dynamic array and use ReDim; Erase itself does not change fixed-array bounds.

Erase, ReDim, and clearing values compared

These operations are related, but they are not interchangeable in every situation:

  • Erase arrayName releases a dynamic array’s allocation, or resets the elements of a fixed-size array.
  • ReDim arrayName(bounds) allocates or resizes a dynamic array. Without Preserve, it initializes the newly dimensioned array rather than retaining its previous contents.
  • ReDim Preserve arrayName(bounds) resizes a dynamic array while retaining existing values that fit the new bounds. In VBA, only the upper bound of the last dimension can be changed when using Preserve.
  • A loop that assigns default values clears or overwrites elements while keeping the array allocated and its bounds unchanged.

For example, if a dynamic array has become unallocated and should be made three elements long, use ReDim. If it is already allocated and you want to keep its contents while increasing the last dimension, use ReDim Preserve. If you want to discard its contents and allocation altogether, use Erase. These choices communicate the intended lifecycle more clearly than repeatedly resizing without a reason.

Clear values but keep the allocation

Sometimes “clear” means “make every value the default again” while retaining the array’s current bounds. In that case, do not erase a dynamic array, because it would become unallocated. Loop through its valid index range and assign the appropriate default for its element type. The example below resets a dynamic array of Long values to zero while keeping its allocation.

Save a copy of the workbook before running a macro. VBA macros cannot be undone with Ctrl+Z.

Sub ResetLongValues()
    Dim values() As Long
    Dim i As Long

    ' Allocate the array and populate it.
    ReDim values(1 To 4)
    For i = LBound(values) To UBound(values)
        values(i) = i * 10
    Next i

    ' Set each element to the default value for Long.
    For i = LBound(values) To UBound(values)
        values(i) = 0
    Next i
End Sub

For a string array, assign vbNullString or Empty-like text as appropriate; for an object array, use Set item = Nothing for each element. A typed numeric array cannot hold Empty as a special value, so zero is its natural cleared value. If zero is meaningful data in your program, clearing to zero may not distinguish an unused slot from a real zero; consider a separate Boolean flag or a different data structure.

Multidimensional arrays and bounds

Erase applies to the whole array, including a multidimensional array. It does not let you clear just one row, column, or selected cell. To reset only part of an array, loop over the desired indexes and assign the appropriate default to those elements. Use LBound and UBound to obtain valid bounds instead of assuming that every array starts at zero or one; VBA arrays can have different lower bounds.

For a dynamic two-dimensional array, ReDim sets bounds for both dimensions. If you later use ReDim Preserve, VBA permits changing only the last dimension’s upper bound. That restriction concerns resizing while keeping values; it does not prevent Erase from releasing the entire dynamic array.

Common errors and misconceptions

  • Using the array after erasing a dynamic array: An unallocated array has no usable element bounds. Reallocate it with ReDim first.
  • Assuming Erase always means “fill with zeros”: That is not what happens to a dynamic array. It is deallocated. A fixed-size numeric array is reset to zeros.
  • Calling UBound before allocation: UBound on an unallocated dynamic array can raise runtime error 9, “Subscript out of range.” Keep track of whether the array has been allocated, or structure the code so bounds are only checked after allocation.
  • Confusing an empty array with an unallocated array: An allocated array has bounds and elements, even if every element holds a default value. An erased dynamic array has no allocated elements to inspect.
  • Expecting Erase to clear worksheet cells: It applies to a VBA array variable, not to a worksheet range. Use Excel’s range methods for cells.

A common source of bugs is code that erases an array in one branch and then assumes the array is still allocated in another. If multiple procedures share an array or pass it between routines, make its allocation state clear in the program design. VBA does not supply a simple general-purpose IsAllocated test for every array type, so tracking allocation with a Boolean variable can be easier to maintain than relying on an error to detect the state.

VBA arrays versus worksheet ranges

A VBA array is data held by a variable while code runs; a worksheet range is a set of cells in the workbook. To remove a range’s values and formulas while leaving its formatting in place, Excel VBA offers ClearContents. For example, Range("A1:A10").ClearContents acts on cells, not on an array named values. The range’s formatting remains, but its contents are removed.

Arrays are often used to process worksheet data in memory and then write results back to a range. Erasing the array does not clear the cells from which its data came, and clearing cells does not erase a separate array variable that contains copied values. Treat those as separate operations, and perform each one only when you intend to change that particular data.

Choosing the right method

Choose based on what you need after clearing, not just on the word “clear.” Use this quick guide:

  • Use Erase for a dynamic array you are finished with and may later allocate at a different size.
  • Use Erase for a fixed-size array when you want all elements reset but the same bounds retained.
  • Use a loop when a dynamic array must stay allocated and its elements should be reset in place, or when only selected elements should change.
  • Use ReDim when you need to allocate or replace the dimensions of a dynamic array and do not need its previous values.
  • Use ReDim Preserve when resizing the final dimension of a dynamic array while retaining data.
  • Use a range method such as ClearContents when the target is worksheet cells, not an in-memory VBA array.

For large arrays, releasing the allocation can be useful when the data is no longer needed, especially before allocating another large structure. If the same array will be reused with the same bounds, resetting values may better express that intention and avoid rebuilding its dimensions. The best choice is the one that makes the array’s next valid use obvious to the reader of the code.

Frequently Asked Questions

Does Erase delete a VBA array?

It does not delete the variable. For a dynamic array, it releases the allocated storage and leaves the array unallocated. For a fixed-size array, it resets the elements while keeping the declared bounds.

How do I clear a dynamic array and use it again?

Run Erase arrayName to release it, then use ReDim arrayName(bounds) before accessing elements again. Set the bounds to the range your next operation requires.

Does Erase set every array element to zero?

Only fixed-size numeric arrays are reset to their numeric default, zero. Erasing a dynamic array deallocates it; other fixed-array element types reset to their own defaults, such as an empty string or Nothing.

Can I clear one element without erasing the whole array?

Yes. Assign that element its appropriate default value, such as zero for a Long, an empty string for a string, or Nothing for an object reference. This leaves the other elements and the array’s allocation unchanged.

Is Erase the same as ClearContents in Excel?

No. Erase resets or deallocates a VBA array. ClearContents removes values and formulas from worksheet cells while leaving their formatting in place.

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.