VBA Removing Items From a Collection


Example

Items are removed from a Collection by calling its .Remove method:

Syntax:

.Remove(index)
ParameterDescription
indexThe 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 Collection will change the numeric indexes of all the items after it in the Collection. For loops 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 Collection from inside of a For Each loop 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