VBA Collections Removing Items From a Collection
Example
Items are removed from a Collection by calling its .Remove method:
Syntax:
.Remove(index)
| Parameter | Description |
|---|---|
| index | The item to remove from the Collection. If the value passed is a numeric type or Variant with a numeric sub-type, it will be interpreted as a numeric index. If the value passed is a String or Variant containing a string, it will be interpreted as the a key. If a String key is passed that does not exist in the Collection, a Run-time error 5: "Invalid procedure call or argument" will result. If a numeric index is passed that is does not exist in the Collection, a Run-time error 9: "Subscript out of range" will result. |
Notes:
- Removing an item from a
Collectionwill change the numeric indexes of all the items after it in theCollection.Forloops that use numeric indexes and remove items should run backwards (Step -1) to prevent subscript exceptions and skipped items. - Items should generally not be removed from a
Collectionfrom inside of aFor Eachloop as it can give unpredictable results.
Sample Usage:
Public Sub Example()
Dim foo As New Collection
With foo
.Add "One"
.Add "Two", "Second"
.Add "Three"
.Add "Four"
End With
foo.Remove 1 'Removes the first item.
foo.Remove "Second" 'Removes the item with key "Second".
foo.Remove foo.Count 'Removes the last item.
Dim member As Variant
For Each member In foo
Debug.Print member 'Prints "Three"
Next
End Sub
