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 keydeletes one matching key and its item.dict.RemoveAlldeletes every key-item pair.Set dict = Nothingreleases 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:
Removetakes a key, not the associated item. - Missing key: guard a possible deletion with
Existsto 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
RemoveAllclears 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
- What Is Power Query? Excel Data Cleanup Explained
- AI and Power Query: What It Can and Cannot Do in Excel
- Power Query Split Cell Into Rows: Steps and Examples
Try it: the Column Letter to Number: aA is 27, XFD is 16,384. Converts both ways.
More in VBA & macros
- Excel VBA UserForm Tab Order: Set It CorrectlyTo set an Excel VBA UserForm’s tab order, open the form in the Visual Basic Editor, choose View…
- VBA AutoFilter Method of Range Class Failed?“AutoFilter method of Range class failed” is usually Excel’s run-time error 1004: VBA tried to apply a filter…
- VBA UserForm: How to Show It in ExcelTo show a VBA UserForm in Excel, call its name followed by .Show, such as UserForm1.Show. Add vbModeless…
- Excel Macro to Protect a Sheet: VBA Code and SetupTo protect an Excel worksheet with a macro, call the sheet’s VBA Protect method, for example Worksheets("Entry").Protect Password:="change-me".…
- VBA Copy Paste Formats: Methods and ExamplesIn Excel VBA, copy formatting without changing cell contents by copying a source range and pasting with Paste:=xlPasteFormats.…
- Excel VBA Active Range: What Selection Really MeansExcel VBA does not have a built-in ActiveRange property. To refer to the range currently selected in Excel,…
Sources: Function behaviour and menu paths: Microsoft Support documentation for Excel. Method & sources
Spotted an error in this guide? Let us know.