Erase Statement

The Erase Statement can be used to reinitialise arrays.
This statement can be used on both Fixed Size and Dynamic Size arrays although the behaviour is slightly different.
When you first declare a fixed-length string, it contains null characters.

Fixed Size Arrays

When used on a fixed array this statement will reset all the values to their default values.
No memory is recovered.

Dynamic Size Arrays

When used on a dynamic array this statement will actually erase all the items in the entire array.
If you want to use the array again you must use the ReDim statement to re-declare the size.
The memory will be recovered.

Numeric Arrays

Keeps the items and resets them all to zero.

Public FixedSize_Numeric(5, 5) As Integer 

Public Sub Numeric_FixedSize_1() 
   FixedSize_Numeric(1, 1) = 10 
   Erase FixedSize_Numeric 
End Sub 

Removes all the elements.

Public DynamicSize_Numeric() As Integer 

Public Sub Numeric_DynamicSize_2() 
   ReDim DynamicSize_Numeric(5, 5) 
   DynamicSize_Numeric(1, 1) = 10 
   Erase DynamicSize_Numeric 
End Sub 

String Arrays

Values set to zero-length string in a variable array.
Values set to 0 in a fixed array.
Keeps the items and resets them all to empty string "".

Public FixedSize_StringVariableLength(5, 5) As String 

Public Sub StringVariableLength_FixedSize_3() 
   FixedSize_StringVariableLength(1, 1) = "text" 
   Erase FixedSize_StringVariableLength 
End Sub 

Removes all the elements.

Public DynamicSize_StringVariableLength() As String 

Public Sub StringVariableLength_DynamicSize_4() 
   ReDim DynamicSize_StringVariableLength(5, 5) 
   DynamicSize_StringVariableLength(1, 1) = "text" 
   Erase DynamicSize_StringVariableLength 
End Sub 

Keeps the items and resets them all to empty string "".

Public FixedSize_StringFixedLength(5, 5) As String * 10 

Public Sub StringFixedLength_FixedSize_5() 
   FixedSize_StringFixedLength(1, 1) = "text" 
   Erase FixedSize_StringFixedLength 
End Sub 

Removes all the elements.

Public DynamicSize_StringFixedLength() As String * 10 

Public Sub StringFixedLength_DynamicSize_6() 
   ReDim DynamicSize_StringFixedLength(5, 5) 
   DynamicSize_StringFixedLength(1, 1) = "text" 
   Erase DynamicSize_StringFixedLength 
End Sub 

Variant Arrays

Keep all the items and resets them all to Empty.

Public Variant_FixedSize(5, 5) As Variant 

Public Sub FixedSize_Variant() 
   Variant_FixedSize(1, 1) = "text" 
   Erase Variant_FixedSize 
End Sub 

Removes all the elements.

Public DynamicSize_Variant() As Variant 

Public Sub Variant_DynamicSize_8() 
   ReDim DynamicSize_Variant(5, 5) 
   DynamicSize_Variant(1, 1) = "text" 
   Erase DynamicSize_Variant 
End Sub 

Object Arrays

Keep all the items and resets them all to Nothing.

Public FixedSize_Object(5, 5) As Object 

Public Sub Object_FixedSize_9() 
   Set FixedSize_Object(1, 1) = ThisWorkbook 
   Erase FixedSize_Object 
End Sub 

Removes all the elements.

Public DynamicSize_Object() As Object 

Public Sub Object_DynamicSize_10() 
   ReDim DynamicSize_Object(5, 5) 
   Set DynamicSize_Object(1, 1) = ThisWorkbook 
   Erase DynamicSize_Object 
End Sub 

Important

Fixed arrays keep their dimensions after erasing.
Dynamic arrays lose their dimensions after erasing.
The Erase statement cannot be used at class or module level. It can only be used inside a subroutine or function.
The Erase statement is equivalent to assigning the Nothing keyword to each array variable.