VBA Dictionary Remove Key: Safe Removal and Common Errors

VBA & macros · Updated November 18, 2026

To remove one entry from a VBA Dictionary, call its Remove method with the key: dict.Remove key. If the key might be missing, check dict.Exists(key) first; use RemoveAll only when you intend to empty the entire dictionary.

What a dictionary key represents

A Dictionary stores pairs: a key and its associated item. For example, a key such as "Apples" might point to the number 12. Removing the key removes that whole pair, not just the item value. After removal, the key no longer appears in the dictionary, and its former item cannot be retrieved through that key.

In Excel VBA, Dictionary objects are commonly created using the Windows Scripting Runtime. This is a COM object rather than a built-in VBA collection type, so how you create it can affect portability and editor support. The removal method itself is straightforward once you have a Dictionary object.

Remove a key with a short example

This example creates a late-bound Dictionary, adds two entries, then removes the entry whose key is "Apples". The remaining entry is "Pears", with an item of 8.

Save a copy of the workbook before running a macro. VBA macros cannot be undone with Ctrl+Z.

Option Explicit

Sub RemoveOneDictionaryKey()
    Dim dict As Object

    ' Create a Dictionary using late binding.
    Set dict = CreateObject("Scripting.Dictionary")

    dict.Add "Apples", 12
    dict.Add "Pears", 8

    ' Remove the key and its associated item.
    dict.Remove "Apples"

    Debug.Print dict.Count       ' 1
    Debug.Print dict.Exists("Pears")   ' True

    Set dict = Nothing
End Sub

Remove expects a key, not an item. In this example, dict.Remove "Apples" is correct; dict.Remove 12 would try to remove a key equal to 12, not find the entry whose item happens to be 12. That distinction matters when keys and items have similar-looking values.

Handle a key that may not exist

Calling Remove for a key that is not present raises a runtime error. If a key comes from user input, a worksheet cell, or another process, test for its existence before removing it. That makes the procedure predictable and avoids relying on an error handler for ordinary control flow.

Save a copy of the workbook before running this macro; its changes cannot be reversed with Ctrl+Z.

Option Explicit

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

    Set dict = CreateObject("Scripting.Dictionary")
    dict.Add "Apples", 12
    keyToRemove = "Oranges"

    ' Remove only when the key is present.
    If dict.Exists(keyToRemove) Then
        dict.Remove keyToRemove
    End If

    Set dict = Nothing
End Sub

In this example, "Oranges" is absent, so the condition is false and no removal is attempted. If the key is present, the method removes it. A variation can report the outcome with a message or return a Boolean value, which is useful when other code needs to know whether a deletion actually happened.

Remove several matching keys safely

A common task is deleting every entry that meets a condition, such as removing records whose item is blank. Avoid changing the Dictionary while directly walking its live key collection unless you have checked the behavior for your exact code. A practical approach is to take a snapshot of its keys first, then inspect that snapshot and remove matching entries from the original Dictionary.

Save a copy of the workbook before running this macro, because macro changes cannot be undone with Ctrl+Z.

Option Explicit

Sub RemoveBlankItems()
    Dim dict As Object
    Dim keyList As Variant
    Dim key As Variant

    Set dict = CreateObject("Scripting.Dictionary")
    dict.Add "A100", "Ready"
    dict.Add "A101", ""
    dict.Add "A102", "Hold"

    ' Keys returns a snapshot array for this pass.
    keyList = dict.Keys

    For Each key In keyList
        If dict(key) = "" Then
            dict.Remove key
        End If
    Next key

    Debug.Print dict.Count   ' 2
    Set dict = Nothing
End Sub

The condition here treats an empty-string item as the criterion. If items may contain numbers, objects, or Null values, adapt the test to those data types instead of assuming every item can safely be compared with an empty string. Also consider whether a missing value, an empty string, and a cell containing a formula that returns an empty string should all count as “blank” in your application.

Remove one entry or clear everything?

Use Remove when you know which key to delete. Use RemoveAll when every key-item pair should be cleared but you still want to keep using the same Dictionary object. For example, a procedure might reuse a Dictionary for a new batch of data after clearing the previous batch.

  • dict.Remove key deletes one matching key and its item.
  • dict.RemoveAll deletes every key-item pair.
  • Set dict = Nothing releases your variable’s reference to the object; it is not a substitute for choosing which entries to remove.

After RemoveAll, the object remains available and can accept new entries. After Set dict = Nothing, the variable no longer refers to that object. If the object is no longer needed, releasing the reference is often appropriate; if it will be reused, clear it rather than discarding it.

Dictionary setup, references, and Mac compatibility

There are two common ways to declare a Dictionary. Late binding, shown above, uses As Object and CreateObject("Scripting.Dictionary"). It avoids adding a reference in the VBA project and is often convenient when sharing a workbook among Windows users. The trade-off is that the editor cannot provide the same Dictionary-specific autocomplete and compile-time checking.

Early binding uses the Microsoft Scripting Runtime reference. In the Visual Basic Editor on Windows, choose Tools > References, select Microsoft Scripting Runtime, and declare a variable as Scripting.Dictionary. Early binding can make code easier to develop, but a project using that reference depends on the runtime being available and correctly referenced on the target computer.

The Scripting Runtime is a Windows component, so code that creates Scripting.Dictionary with CreateObject should not be assumed to work in Excel for Mac. If a workbook must run on both Windows and Mac, verify the object is supported in the exact environments you target, or choose a cross-platform design such as a VBA Collection or arrays and custom lookup logic. A Collection does not offer the same convenient Exists method, so its lookup and deletion code is different.

Key comparison and common removal problems

Whether a string key is considered equal to another string depends on the Dictionary’s CompareMode. The default is binary comparison, which generally treats letter case as significant. If you want case-insensitive text keys, set CompareMode to vbTextCompare before adding entries. Changing the comparison mode after the Dictionary already contains data raises an error, so choose it during setup.

For example, with text comparison enabled, a lookup for "apples" can match a stored key "Apples". With binary comparison, the casing can make those keys distinct. Therefore, if Exists says a key is absent even though it looks similar, check for differences in capitalization, leading or trailing spaces, and the chosen comparison mode. A value copied from a worksheet may contain spaces that are not obvious on screen.

  • Wrong argument: Remove takes a key, not the associated item.
  • Missing key: guard a possible deletion with Exists to avoid a runtime error.
  • Comparison mismatch: confirm capitalization, spaces, and CompareMode.
  • Wrong object: verify that the variable has been set to a Dictionary before calling its methods.
  • Unexpectedly empty object: remember that RemoveAll clears every entry, not just the one currently being processed.

When to use error handling

An If dict.Exists(key) Then check is usually clearest when you expect that a key may or may not be present. Error handling is more appropriate when an operation can fail for several reasons that should be handled together, or when the code must attempt an operation without a separate check. Avoid using broad On Error Resume Next around a long section of code: it can hide errors unrelated to the missing key. If you do use it, limit its scope and check Err carefully.

There is a small design choice between checking first and attempting removal: the existence check makes the intent explicit, while attempting removal avoids a separate lookup. For ordinary VBA procedures, the extra check is generally a useful clarity and safety improvement. If the Dictionary could be changed by other code between the check and the removal, the check alone does not make the two operations atomic; VBA Dictionary use in a typical macro is usually sequential, but shared or asynchronous designs need more care.

Frequently Asked Questions

How do I remove a key from a VBA Dictionary?
Use dict.Remove key. The key and its associated item are both removed. If the key might not exist, first test dict.Exists(key).

What happens if I remove a key that is not in the Dictionary?
Remove raises a runtime error when the specified key is absent. Check with Exists before calling it when absence is possible.

Does Remove delete the key or the item?
It deletes the key-item pair. Pass the key to Remove; passing the item’s value does not search for and delete an entry by its item.

How can I delete every key from a VBA Dictionary?
Call dict.RemoveAll. This empties the Dictionary but leaves the object available for adding new entries.

Can I remove a Dictionary key by its item value?
Not directly with Remove; it accepts a key. To remove entries based on item values, loop over a snapshot from dict.Keys, test each corresponding item, and remove matching keys.

Choosing the right removal pattern

For one known key, use Remove; for a possibly absent key, pair it with Exists; for a full reset, use RemoveAll. For condition-based deletion, inspect a snapshot of the keys so you can safely change the Dictionary during the pass. These patterns keep the code’s intent clear and help prevent accidental removal of the wrong data.

Related

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.