Erase Statement

The Erase Statement can be used to reinitialise arrays.
This statement can be used on both Fixed Size and Dynamic Size arrays.
Erase frees the memory for dynamic size arrays.
Erase recovers no memory for fixed size arrays.
When you first dimension a fixed-length string, it contains null characters.
Erase behaves differently for fixed or dynamic arrays.

Numeric Arrays

Keeps the items and resets them all to zero.

Public Numeric_FixedSize(5, 5) As Integer 

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

Removes all the elements.

Public Numeric_DynamicSize() As Integer 

Public Sub Numeric_DynamicSize_2() 
   ReDim Numeric_DynamicSize(5, 5) 
   Numeric_DynamicSize(1, 1) = 10 
   Erase Numeric_DynamicSize 
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 StringVariableLength_FixedSize(5, 5) As String 

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

Removes all the elements.

Public StringVariableLength_DynamicSize() As String 

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

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

Public StringFixedLength_FixedSize(5, 5) As String * 10 

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

Removes all the elements.

Public StringFixedLength_DynamicSize() As String * 10 

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

Variant Arrays

Keep all the items and resets them all to Empty.

Public Variant_FixedSize(5, 5) As Variant 

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

Removes all the elements.

Public Variant_DynamicSize() As Variant 

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

Object Arrays

Keep all the items and resets them all to Nothing.

Public Object_FixedSize(5, 5) As Object 

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

Removes all the elements.

Public Object_DynamicSize() As Object 

Public Sub Object_DynamicSize_10() 
   ReDim Object_DynamicSize(5, 5) 
   Set Object_DynamicSize(1, 1) = ThisWorkbook 
   Erase Object_DynamicSize 
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.