VBA Clear Array – Erase Function
In this Article
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:
|
1 |
Erase arrExample |
In practice:
|
1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 |
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.
|
1 2 |
'Erase Entire Array ReDim arrExample(1 To 4) |
Full Example:
|
1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 |
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.
|
1 2 |
'Erase Position 3+ ReDim Preserve arrExample(1 To 2) |
Full Example:
|
1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 17 18 19 |
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.
