Excel VBA Array Clear: How to Erase or Reset Values

VBA & macros · Updated November 21, 2026

To clear a VBA array, use the Erase statement: Erase arrayName. For a fixed-size array, this resets its elements to their default values; for a dynamic array, it releases the array’s allocated memory, so you must use ReDim before using the array again.

What “clear an array” means in VBA

An array is a variable that holds multiple values, usually accessed by an index. For example, scores(1) refers to one element of an array named scores. Clearing an array can mean either resetting those values or discarding the array’s current storage. VBA’s Erase statement does one or the other depending on how the array was declared.

This distinction matters because an array declared with explicit bounds is fixed-size, while an array declared with empty parentheses is dynamic. The declaration determines what happens when you erase it, not whether its elements currently contain values. Clearing an array in memory also does not clear worksheet cells: the two operations use different objects and different commands.

In short, use Erase when you want to reset or release an array. Use Range.ClearContents when you want to remove cell values. If you want an array to keep its allocated bounds while you replace selected entries, assign new values or loop through its elements instead.

Fixed-size arrays: reset elements, keep the bounds

A fixed-size array has bounds in its declaration, such as Dim scores(1 To 3) As Long. Erasing it resets each element to the default value for its data type, but the array remains available with the same bounds. For a numeric type such as Long, the reset value is zero. String elements become zero-length strings, and Variant elements become Empty. Object variables are reset to Nothing.

For example, the following procedure clears three values without changing the array’s declared range. Make a copy of your workbook before running a macro: VBA actions cannot be undone with Ctrl+Z.

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

    ' Put example values in the fixed-size array.
    scores(1) = 72
    scores(2) = 85
    scores(3) = 91

    ' Reset each element to its default value (zero for Long).
    Erase scores
End Sub

After Erase scores, the array still has indexes 1 through 3, and each element is zero. You can assign new values to those same indexes without using ReDim. The same principle applies to fixed-size arrays declared with the Static keyword: Erase resets their elements, but does not remove the fixed allocation.

Dynamic arrays: release memory and allocate again

A dynamic array is declared with empty parentheses, such as Dim scores() As Long. It has no usable bounds until you allocate them with ReDim. When you apply Erase to an allocated dynamic array, VBA releases its current storage. The array variable remains declared, but you cannot read or assign an element until you allocate the array again.

Use ReDim to allocate it again, choosing bounds that suit the next set of data. The following example creates three elements, erases them, and then allocates five new elements:

Sub ClearAndResizeDynamicArray()
    Dim scores() As Long

    ' Allocate and fill the first set of elements.
    ReDim scores(1 To 3)
    scores(1) = 72
    scores(2) = 85
    scores(3) = 91

    ' Release the current allocation.
    Erase scores

    ' Allocate a new array before using its elements again.
    ReDim scores(1 To 5)
    scores(1) = 100
End Sub

After the second ReDim, the array has indexes 1 through 5. Its elements start with their data type’s default values, except for any values you assign afterward. Erasing a dynamic array does not permanently disable it; it can be allocated repeatedly during a procedure or across later operations.

Choosing between Erase, ReDim, and assigning values

The right operation depends on whether you want to keep the array’s bounds, discard its allocation, or preserve some existing data. Use these distinctions as a quick guide:

  • Use Erase on a fixed-size array to reset all elements while retaining the array’s declared bounds.
  • Use Erase on a dynamic array to release its current allocation; use ReDim before accessing its elements again.
  • Use ReDim to establish or change a dynamic array’s bounds. A plain ReDim discards existing values when it changes the allocation.
  • Use ReDim Preserve when resizing a dynamic array and retaining its existing values is important. It has limitations: VBA only permits the last dimension to change when using Preserve.
  • Assign values individually or in a loop when you want to replace particular elements but keep the array and its bounds.

For instance, calling ReDim scores(1 To 5) on an already allocated dynamic array is not a substitute for clearing it while preserving its values. A normal resize can discard the old contents. If your goal is to preserve data while extending the last dimension, use a valid ReDim Preserve operation instead. If your goal is simply to start over, Erase followed by ReDim makes that intention explicit.

Clearing values while keeping a dynamic array allocated

Sometimes a dynamic array should keep the same bounds because later code expects those indexes to exist. In that case, do not erase the array. Set each element to the value that represents “blank” for your application. This is also useful when only some elements need clearing.

For a numeric array, assigning zero is a straightforward reset when zero is an appropriate empty value. But zero is still a valid number, so it cannot distinguish an unused entry from a real entry whose value is zero. If that distinction matters, consider storing a separate Boolean flag or using a Variant array where an Empty value has a meaningful role. Choose the representation deliberately rather than treating every zero as blank.

For a fixed-size array, Erase is usually simpler than a loop if every element should return to its default. For a dynamic array that must remain allocated, a loop is one option. A loop over an unallocated array will fail when it tries to get its bounds, so ensure the array has been allocated before using LBound or UBound. Likewise, an erased dynamic array must be reallocated before code accesses an indexed element.

Array bounds and checking whether an array is allocated

VBA arrays do not always start at index zero. Their lower bound can depend on the declaration, an explicit ReDim range, or the module’s Option Base setting. Code that processes an array should use LBound(arrayName) and UBound(arrayName) rather than assuming indexes start at zero or one.

For example, a loop intended to visit every element should be based on the array’s actual bounds. However, calling LBound or UBound on an unallocated dynamic array raises an error. IsArray tells you whether a Variant contains an array, but it is not a reliable test that a particular dynamic array has been allocated. If allocation status can vary, track that state in your code or use carefully managed error handling before querying the bounds.

A related misconception is that Erase changes the lower bound to zero or removes a fixed array’s bounds. It does neither. A fixed-size array keeps its declared bounds; a dynamic array has no current allocation after erasing and receives bounds only when it is allocated again with ReDim.

Clearing an array is not the same as clearing worksheet cells

A VBA array is held in memory as part of a macro’s data. Worksheet cells are part of an Excel range. Erase scores does not remove anything from a worksheet, and Range.ClearContents does not reset a separate VBA array. If a macro reads a range into an array, editing one does not automatically update the other; your code must explicitly transfer values between them.

To clear values and formulas from cells while leaving formatting in place, use ClearContents. To remove cell contents and formatting, the Range object’s Clear method has a broader effect. For example, this macro clears contents in A2:C20 on a worksheet named Data, but leaves the cells’ formatting intact. Make a copy of the workbook before running it because macro changes cannot be reversed with Ctrl+Z.

Sub ClearWorksheetContents()
    Dim ws As Worksheet

    ' Point to the worksheet whose cells should be cleared.
    Set ws = ThisWorkbook.Worksheets("Data")

    ' Remove values and formulas but keep cell formatting.
    ws.Range("A2:C20").ClearContents
End Sub

Excel for the web can open macro-enabled workbooks, but it cannot run or edit VBA macros. Use the desktop Excel application to work with this code. In Microsoft 365 for Windows, Alt+F11 opens the Visual Basic Editor; on Mac, Option+F11 may require the Fn key depending on the keyboard and system settings. Save a workbook containing VBA as a macro-enabled file, such as .xlsm, so the code can be retained.

Common errors and practical safeguards

The most common problem after erasing a dynamic array is trying to use it as though it still had elements. A line such as scores(1) = 10 cannot succeed after Erase scores until the array has been allocated with ReDim. Put allocation in the same logical branch that prepares the data, and avoid calling bounds functions until allocation is known.

  • “Subscript out of range” or an invalid index: check that the array is allocated and that the index falls between LBound and UBound.
  • Unexpected loss of values after resizing: a plain ReDim can discard the current contents. Use ReDim Preserve only when its dimension rules fit your array.
  • Worksheet cells remain unchanged: Erase affects an array variable, not the cells from which values may have been read.
  • Trying to erase only one element: Erase operates on the array, not an individual indexed element. Assign a suitable default value directly to the element you want to reset.

Macros can make changes that do not appear in Excel’s Undo history, and Ctrl+Z cannot undo VBA execution. Before running a procedure that changes important data, save a separate copy of the workbook. When a macro clears both an in-memory array and worksheet data, treat those as two separate operations and confirm that each target is correct.

Frequently Asked Questions

What is the VBA command to clear an array?
Use Erase arrayName. A fixed-size array’s elements reset to their data type’s defaults. A dynamic array’s allocation is released, so use ReDim before accessing it again.

Can I use Erase on a dynamic array and then reuse it?
Yes. After Erase, allocate the array again with ReDim, then assign or read its elements. Do not index it between erasing and reallocating.

Does Erase set every array value to zero?
Only numeric elements reset to numeric defaults such as zero. Strings, Variants, and object references have different default states. For a dynamic array, Erase releases the allocation instead of leaving an allocated array full of zeros.

How do I clear one array element?
Assign a suitable default directly to that index, such as zero for a numeric element or an empty string for a string element. Choose a value that your program can distinguish correctly from valid data.

How do I clear cells instead of a VBA array?
Use a worksheet range’s ClearContents method to remove values and formulas while keeping formatting. Use Clear if you also intend to remove formatting. These range methods are separate from the Erase statement.

Can I run Excel VBA in a browser?
No. Excel for the web does not run or edit VBA macros. Open the workbook in desktop Excel to use the Visual Basic Editor and run VBA code.

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.