VBA Collections Clearing All Items From a Collection
Example
The easiest way to clear all of the items from a Collection is to simply replace it with a new Collection and let the old one go out of scope:
Public Sub Example()
Dim foo As New Collection
With foo
.Add "One"
.Add "Two"
.Add "Three"
End With
Debug.Print foo.Count 'Prints 3
Set foo = New Collection
Debug.Print foo.Count 'Prints 0
End Sub
However, if there are multiple references to the Collection held, this method will only give you an empty Collection for the variable that is assigned.
Public Sub Example()
Dim foo As New Collection
Dim bar As Collection
With foo
.Add "One"
.Add "Two"
.Add "Three"
End With
Set bar = foo
Set foo = New Collection
Debug.Print foo.Count 'Prints 0
Debug.Print bar.Count 'Prints 3
End Sub
In this case, the easiest way to clear the contents is by looping through the number of items in the Collection and repeatedly remove the lowest item:
Public Sub ClearCollection(ByRef container As Collection)
Dim index As Long
For index = 1 To container.Count
container.Remove 1
Next
End Sub
