VBA Delete Dictionary Items, Keys, or the Whole Object

In VBA, remove one entry from a Scripting.Dictionary with dict.Remove key, clear every entry but keep the dictionary with dict.RemoveAll, or release your object reference with Set dict = Nothing. These actions are different: choose the one that matches whether you want to delete a key-value pair, empty the dictionary, or stop using the object.

What “delete a dictionary” can mean

VBA developers often use “delete” to describe several operations, but the dictionary object has no general-purpose Delete method. Its relevant methods are Remove, which removes one key and its associated item, and RemoveAll, which removes every pair. Setting an object variable to Nothing is separate: it releases that variable’s reference to the object.

A dictionary stores key-item pairs. For example, a key such as "North" might map to a number, a name, or another object. Removing the key also removes access to its associated item through that dictionary. It does not delete a worksheet, a file, or some other external resource just because the item refers to one.

dict.Remove key: remove one pair.
dict.RemoveAll: empty the dictionary so it can be reused.
Set dict = Nothing: release the current variable’s object reference.

Remove one key safely

Call Exists before Remove when a key might not be present. Attempting to remove a missing key can raise a runtime error, so checking first is more reliable than letting the macro stop unexpectedly. This example uses late binding, which avoids setting a VBA reference to the Scripting Runtime on Windows.

Save a copy of the workbook before running a macro: VBA changes cannot be undone with Ctrl+Z. The same warning applies to the examples below.

Sub RemoveOneDictionaryItem()
    Dim dict As Object
    Dim keyToRemove As String

    ' Create a Scripting.Dictionary on Windows.
    Set dict = CreateObject("Scripting.Dictionary")
    dict.Add "North", 12
    dict.Add "South", 8

    keyToRemove = "North"
    ' Avoid an error if the requested key is absent.
    If dict.Exists(keyToRemove) Then
        dict.Remove keyToRemove
    End If

    Debug.Print dict.Count  ' Prints 1.
    Set dict = Nothing
End Sub

The key passed to Remove must identify the key in the dictionary. If your keys are not strings, declare the key variable with a suitable type or use a Variant. A dictionary can use different key types, but using one consistent type for a particular set of keys makes code easier to reason about.

Clear every item and reuse the dictionary

Use RemoveAll when the dictionary itself is still useful but its current contents are not. The object remains allocated, its Count becomes zero, and you can add new entries afterward. This is often the right choice inside a loop that rebuilds a lookup table for each worksheet or batch of records.

Sub ClearDictionaryContents()
    Dim dict As Object

    ' Create and populate a Windows Scripting.Dictionary.
    Set dict = CreateObject("Scripting.Dictionary")
    dict.Add "North", 12
    dict.Add "South", 8

    ' Remove every key-item pair; keep the object available.
    dict.RemoveAll
    Debug.Print dict.Count  ' Prints 0.

    dict.Add "East", 5     ' The cleared dictionary can be reused.
    Debug.Print dict.Count  ' Prints 1.
    Set dict = Nothing
End Sub

Do not confuse an empty dictionary with an uninitialized object variable. After RemoveAll, the object still exists and methods such as Add and Exists can be called. After Set dict = Nothing, calling a method through dict will fail because that variable no longer refers to an object.

Release the dictionary object

For an object variable, VBA uses Set for assignment. Therefore, to release a reference, write Set dict = Nothing; writing dict = Nothing is not the correct object-assignment syntax. This does not mean “delete the dictionary’s data everywhere.” It removes the reference held by that variable.

If another variable also refers to the same dictionary, that other reference remains usable. The object is released when no references to it remain. In a short procedure, VBA normally releases local object references when the procedure ends, so explicitly setting a local variable to Nothing is often optional. It can still make the point at which a reference is released clear, especially in longer procedures or when an object should not be used again.

Remove entries that meet a condition

When you need to remove several selected entries, do not change the dictionary while relying on its live contents as the loop’s source. Instead, take a snapshot of its keys with Keys, then remove matching keys from that snapshot-based loop. The following example keeps only the entry whose key is "Keep".

Sub RemoveDictionaryItemsExceptOne()
    Dim dict As Object
    Dim keys As Variant
    Dim key As Variant

    ' Create a Windows Scripting.Dictionary.
    Set dict = CreateObject("Scripting.Dictionary")
    dict.Add "Keep", 1
    dict.Add "OldA", 2
    dict.Add "OldB", 3

    ' Keys returns an array, so removal does not change this loop's source.
    If dict.Count > 0 Then
        keys = dict.Keys
        For Each key In keys
            If CStr(key) <> "Keep" Then dict.Remove key
        Next key
    End If

    Debug.Print dict.Count  ' Prints 1.
    Set dict = Nothing
End Sub

Here the comparison is case-sensitive because the dictionary uses its default comparison mode. If you want text keys treated without regard to letter case, set CompareMode to vbTextCompare before adding the first key. Changing the comparison mode after the dictionary contains data can cause an error. Choose the mode at setup time, and use the same assumptions when checking keys or filtering them.

Choose early binding or late binding

The examples use late binding: Dim dict As Object followed by CreateObject("Scripting.Dictionary"). On Windows, this avoids requiring each user to enable the Scripting Runtime reference in the VBA editor. The trade-off is that the editor offers less dictionary-specific autocomplete and catches fewer type mistakes while you write code.

With early binding, add the Microsoft Scripting Runtime reference in the VBA editor using Tools > References, then declare the variable as Scripting.Dictionary. The declaration provides clearer type information and autocomplete. A workbook that depends on this reference may need the reference available on each computer where it runs; missing or broken references can prevent code from compiling.

Scripting.Dictionary is a Windows COM component and is not a portable choice for Excel VBA projects that must run on Mac. Mac support for COM-based components differs from Windows, so do not assume that CreateObject("Scripting.Dictionary") will work there. For a cross-platform workbook, consider a VBA Collection or a dictionary implementation designed for the target platforms, and verify the chosen approach in the Excel versions you support.

Common errors and how to avoid them

Removing a missing key: test dict.Exists(key) before calling Remove if the key may be absent. This is clearer than suppressing all errors with On Error Resume Next.
Calling a method after releasing the object: do not call RemoveAll, Count, or another method after setting the only usable reference to Nothing. Create or assign an object again first.
Confusing clearing with releasing: use RemoveAll to empty and reuse the dictionary; use Set dict = Nothing to release a reference. They solve different problems.
Changing comparison mode too late: assign CompareMode before adding keys. Decide whether keys should be compared as text without regard to case or by the default binary comparison.
Assuming a key’s value is deleted elsewhere: removing a pair removes it from this dictionary. If the item is an object, another variable may still refer to that object; removing the dictionary entry does not automatically erase the object’s external data.

Dictionary versus Collection

A Collection is a built-in VBA alternative that can store items and optionally associate string keys with them. It does not have the same set of convenient dictionary features: in particular, it has no direct equivalent to the dictionary’s Exists, Keys, and RemoveAll methods. Removing a collection item by key can also require handling the error that occurs when the key is absent. A collection may suit simple portable storage, but a dictionary is usually more direct when code needs frequent key lookups or conditional removal.

Think about portability before choosing. A Windows-only automation macro can use Scripting.Dictionary where available. If the workbook must run on both Windows and Mac, test a platform-neutral design rather than assuming a Windows COM object is present. The best alternative depends on whether you need keyed lookup, case-insensitive keys, easy enumeration, or only a simple ordered group of values.

Frequently Asked Questions

What is the VBA command to delete a dictionary key?
Use dict.Remove key to remove one key and its associated item. Check dict.Exists(key) first if the key may not be present.

How do I delete all items from a VBA dictionary?
Call dict.RemoveAll. This empties the dictionary but leaves the object available for adding new items.

Does Set dict = Nothing clear the dictionary?
It releases the object reference held by dict; it is not the method for clearing entries. Use RemoveAll to empty the dictionary. Other references to the same object, if any, remain.

Why does dict.Remove key cause an error?
A common cause is that the key is not in the dictionary. Test it with Exists before removal, and check that the key’s value and comparison mode match what was added.

Can I use Scripting.Dictionary in Excel for Mac?
It is a Windows COM component and is not a dependable cross-platform choice for Mac VBA. If your workbook must run on Mac, use and test a suitable alternative, such as a Collection, for the features your code needs.

Try it: the Column Letter to Number: aA is 27, XFD is 16,384. Converts both ways.

More in VBA & macros

Sources: Function behaviour and menu paths: Microsoft Support documentation for Excel. Method & sources

Spotted an error in this guide? Let us know.