VBA Clear Array – Erase Function

This tutorial will teach you how to clear an Array in VBA.

Clear Entire Array

To clear an entire array, you can use the Erase Statement:

Erase arrExample

In practice:

Sub ClearArray()
    'Create Static Array
    Dim arrExample(1 to 3) as String
 
    'Define Array Values
    arrExample(1) = "Shelly"
    arrExample(2) = "Steve"
    arrExample(3) = "Neema"
 
    'Erase Entire Array
    Erase arrExample

    'Check Array Value
    MsgBox arrExample(1)
End Sub

Resize and Clear Array

If your Array is dynamic (A dynamic array is an array that can be resized, as opposed to a static array which can not be resized), you can use the ReDim Command to resize the array, clearing the entire array of values.

'Erase Entire Array
ReDim arrExample(1 To 4)

Full Example:

Sub ClearArray2()
    'Create Dynamic Array
    Dim arrExample() As String
    ReDim arrExample(1 To 3)
 
    'Define Array Values
    arrExample(1) = "Shelly"
    arrExample(2) = "Steve"
    arrExample(3) = "Neema"
 
    'Erase Entire Array
    ReDim arrExample(1 To 4)
    
    'Check Array Value
    MsgBox arrExample(1)
End Sub

Clear Part of an Array

As mentioned above, the ReDim Command will resize an array, clearing all values from the array.  Instead you can use ReDim Preserve to resize the array, preserving any existing values. In practice, this can be used to quickly clear part of an array.

'Erase Position 3+
ReDim Preserve arrExample(1 To 2)

Full Example:

Sub ClearArray3()
    'Create Dynamic Array
    Dim arrExample() As String
    ReDim arrExample(1 To 3)
 
    'Define Array Values
    arrExample(1) = "Shelly"
    arrExample(2) = "Steve"
    arrExample(3) = "Neema"
 
    'Erase Position 3+
    ReDim Preserve arrExample(1 To 2)
    
    'Resize to 3 Positions
    ReDim Preserve arrExample(1 To 3)
    
    'Check Array Value
    MsgBox arrExample(3)
End Sub

Loop Through Entire Array – Resetting Values

Instead of clearing array values using Erase or ReDim, you could loop through the entire array, resetting each value.

VBA Coding Made Easy

Stop searching for VBA code online. Learn more about AutoMacro - A VBA Code Builder that allows beginners to code procedures from scratch with minimal coding knowledge and with many time-saving features for all users!


Learn More!