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
- When you erase a fixed array:
- 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.
- An array which has numeric values, erase statement will restet all the elements to zeros.
- An array which as variant data type, erase statement will reset all the elements to empty.
- An array which has objects, erase statement will reset all the elements to nothing.
- An array which has objects, erase statement will reset all the elements if it were a separate variable.
- 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 preparing for an interview, make sure to check out these VBA interview questions.