VBA Clear Array (Erase)

HomeVBA TutorialArraysVBA Clear Array (Erase)

You need to use the “Erase” statement to clear an array in VBA. Add the keyword “Erase” before the name of the array that you want to clear but note that all the values will be reset from the array. In the following array, you have 3 elements and we have used the erase statement to erase all.

Sub myMacro1()

Dim myArray() As Long
ReDim myArray(4)

myArray(1) = 1
myArray(2) = 2
myArray(3) = 3
myArray(4) = 4

ReDim myArray(1 To 5)

MsgBox myArray(3)

End Sub

And with that statement, it has erased all the elements from the array, and when we use the Debug.Print (Immediate Window) to get the array element, there’s nothing left.

Clear Dynamic Array

When you use ReDim it removes all the elements. But you can use the preserve statement to preserve some of the elements and clear an array partially. In the following example, we have an array and we have partially erased the fourth value and then re-defined the elements to 5.

And if you want to clear a dynamic array partially.

Sub myMacro2()

Dim myArray() As Long
ReDim myArray(1 To 4)

myArray(1) = 1
myArray(2) = 2
myArray(3) = 3
myArray(4) = 4

'erase position 4 but preserve the 1,2, and 3
ReDim Preserve myArray(1 To 3)

'redefine the array items to five
ReDim Preserve myArray(1 To 5)

MsgBox myArray(3)

End Sub

Important Points

  1. When you erase a fixed array:
    1. An array which has all the values a string, erase statement will reset all the elements to (variable length) to zero length string “” and (fixed length) to zero.
    2. An array which has numeric values, erase statement will restet all the elements to zeros.
    3. An array which as variant data type, erase statement will reset all the elements to empty.
    4. An array which has objects, erase statement will reset all the elements to nothing.
    5. An array which has objects, erase statement will reset all the elements if it were a separate variable.
  2.   When you erase a dynamic array erase statement frees the space used by the dynamic array.

More on VBA Arrays

VBA Add New Value to the Array | VBA Clear Array | VBA Loop Through an Array | VBA Multi-Dimensional Array | VBA Range to an Array | VBA Search for a Value in an Array | VBA Sort Array | VBA Array Length (Size) | VBA Array with Strings | VBA Dynamic Array | ISARRAY Function | ARRAY Function | VBA Arrays

If you are trying to prepare for an interview check out these VBA Interview Questions and Answers. And if you found this tutorial helpful, you can support us to create more tutorials like this.