VBA 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. In an array that has all the values of a string, erase statement will reset all the elements to (variable length) to the zero-length string “” and (fixed length) to zero.
   2. In an array which has numeric values, erase statement will reset all the elements to zeros.
   3. In an array which as variant data type, erase statements will reset all the elements to empty.
   4. In an array which has objects, erase statement will reset all the elements to nothing.
   5. In 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.