To remove one entry from an Excel VBA Dictionary, call dict.Remove key; to remove every entry but keep the dictionary object, call dict.RemoveAll. Check dict.Exists(key) first if the key might not be present, because removing a missing key causes an error.
What Remove Does in a VBA Dictionary
A Dictionary stores pairs of keys and items. Each key must be unique, and the key is what you pass to Remove. The item can be text, a number, a date, an object reference, or another value VBA can store. Removing an entry deletes the key and its associated item from that dictionary; it does not search for a matching item value.
For example, if a dictionary maps employee IDs to names, dict.Remove "E104" removes the entry for employee ID E104. It does not remove an entry just because its item happens to be a particular name. If you know only the item value, you must find the corresponding key first.
After a successful removal, the dictionary’s Count decreases by one. The dictionary itself remains available, and you can add entries to it again. This makes Remove useful when you want to discard selected records while keeping the rest of a lookup table in memory.
Remove One Entry by Key
This Windows-focused example creates a late-bound dictionary, adds two entries, and removes one by its key. Before running any macro that changes data or workbook state, save a copy of the workbook: VBA macros cannot be undone with Ctrl+Z.
Sub RemoveOneDictionaryEntry()
Dim dict As Object
Dim keyToRemove As String
' Create a Scripting.Dictionary on Windows.
Set dict = CreateObject("Scripting.Dictionary")
dict.Add "A-104", "Open"
dict.Add "B-208", "Closed"
keyToRemove = "A-104"
' Avoid an error if the key is absent.
If dict.Exists(keyToRemove) Then
dict.Remove keyToRemove
End If
Debug.Print dict.Count ' Prints 1
End Sub
The argument can be a variable or a literal key. For instance, dict.Remove keyToRemove and dict.Remove "A-104" both remove the entry when that key exists. The key’s data type and value need to match the key used when the entry was added. In normal code, storing the key in a variable often makes it easier to inspect and reuse.
Removing by key is different from assigning a new item. To change the value associated with an existing key, use its Item property, such as dict("A-104") = "In progress". That changes the item; it does not remove or rename the key.
Remove Every Entry with RemoveAll
Use dict.RemoveAll when you want to empty a dictionary rather than discard the object. The dictionary’s Count becomes zero, and you can reuse the same variable to add new entries. This is different from setting an object variable to Nothing: that releases the variable’s reference to the object rather than calling the method that clears its contents.
For example, after processing one worksheet, a macro might clear its lookup dictionary before loading values from another worksheet. If you no longer need the dictionary at all, you can also release the object reference with Set dict = Nothing; that is a separate operation from clearing its entries. If another variable also refers to that dictionary, releasing just one reference does not make the shared object disappear.
Be careful not to use RemoveAll when you mean to remove only a particular key. It clears every key-item pair, including entries that may still be needed later in the macro.
Removing Entries While Looping
Deleting entries as you enumerate a dictionary can make loop behavior hard to reason about. A reliable pattern is to capture the keys first, then loop over that array and remove matching entries from the dictionary. The following example removes keys that begin with X. Save a copy of the workbook before running a macro, since Ctrl+Z cannot reverse VBA changes.
Sub RemoveKeysStartingWithX()
Dim dict As Object
Dim keys As Variant
Dim key As Variant
Set dict = CreateObject("Scripting.Dictionary")
dict.Add "X-10", "North"
dict.Add "Y-20", "South"
dict.Add "X-30", "East"
' Keys returns an array that can be looped over separately.
keys = dict.Keys
For Each key In keys
If Left$(CStr(key), 1) = "X" Then
dict.Remove key
End If
Next key
Debug.Print dict.Count ' Prints 1
End Sub
The snapshot matters because the loop iterates over the returned key array, not over a collection that is being changed. The example uses Variant for the key because dictionary keys can have different data types. If you are filtering by item values instead, inspect dict(key) during the loop and remove the key when the condition is met.
For a very small dictionary, it may be tempting to remove entries directly inside a For Each loop over the dictionary. Avoid relying on enumeration behavior while changing the collection. Taking a key snapshot is clear, easy to maintain, and avoids skipped or invalidated iteration patterns.
Missing Keys, Exists, and Common Errors
A frequent problem is calling Remove for a key that is not in the dictionary. The method does not silently ignore that request; it raises a runtime error. Use Exists when the key may be absent, as in the first example. This is generally clearer than using broad error suppression such as On Error Resume Next, which can hide unrelated mistakes.
Remember that Exists takes a key, not an item. If a dictionary contains the key "A-104" with item "Open", then dict.Exists("A-104") tests for the entry. dict.Exists("Open") tests whether "Open" is itself a key; it does not search the items.
When debugging an unexpected missing-key error, inspect the value being passed to Remove, confirm that the key was added, and check for differences such as leading or trailing spaces. Also verify that your code has not already removed the entry in an earlier branch.
Key Matching and CompareMode
How text keys compare depends on the dictionary’s CompareMode. The default is binary comparison, so keys whose text differs by letter case are treated as distinct. If you want case-insensitive text keys, set CompareMode to vbTextCompare before adding entries. Do not wait until the dictionary contains data: changing the comparison mode after keys have been added can raise an error.
With text comparison enabled, checking or removing a key does not require the same capitalization as the original key. With binary comparison, it does. Choose the mode deliberately, especially when keys come from worksheet cells where capitalization may vary. CompareMode affects how string keys are compared; it does not turn an item value into a key or remove entries based on their contents.
Creating the Dictionary and Platform Limits
On Windows, CreateObject("Scripting.Dictionary") is a common late-binding approach. It avoids selecting a reference in the VBA editor and is convenient when a workbook will be opened on Windows computers with the required scripting component. In a late-bound declaration, use Dim dict As Object, as in the examples.
Early binding is another Windows option: set a reference to Microsoft Scripting Runtime in the VBA editor and declare the variable as Scripting.Dictionary. Early binding can provide editor assistance and compile-time type information, but another computer may need the reference available for the project to compile. Late binding avoids that particular reference dependency, though it still depends on the runtime object being available.
Excel for the web does not run VBA macros. On Mac, do not assume that the Windows COM-based Scripting.Dictionary object will be available; support can differ from Windows and should be checked in the actual Excel for Mac environment. If a workbook must work across platforms, consider a VBA Collection or a cross-platform dictionary implementation whose compatibility you have verified. Excel’s ribbon paths and keyboard shortcuts are not needed to call the VBA method; the code runs from the VBA editor or a macro entry point.
Dictionary Remove Compared with Alternatives
Use Remove for a specific key, RemoveAll to empty the dictionary, and Set dict = Nothing when the variable no longer needs to refer to the object. They solve different problems. Setting a variable to Nothing is not a substitute for deleting selected entries, and RemoveAll is not a way to release a particular key.
A VBA Collection is built in and can be useful when portability matters, but it does not provide the same convenient Exists method or the same direct key-based removal pattern. If the data is already in worksheet cells and must remain visible or editable, deleting worksheet rows or clearing cells may be more appropriate than using an in-memory dictionary. A dictionary is especially useful for fast lookups and temporary processing, not as a replacement for worksheet data that users need to see.
Frequently Asked Questions
How do I remove a key from a VBA Dictionary?
Call dict.Remove key, using the key associated with the entry. Check dict.Exists(key) first if the key may be missing.
How do I remove all items from a VBA Dictionary?
Call dict.RemoveAll. This empties the dictionary but leaves the object available for reuse.
Does Dictionary.Remove delete an item by its value?
No. It removes an entry by key. To remove an entry based on its item value, loop through the keys, test the corresponding items, and remove the matching keys.
What happens if I remove a key that does not exist?Remove raises a runtime error. Use Exists to test the key first, or handle the specific error deliberately if your design requires it.
Can I remove dictionary entries inside a For Each loop?
It is safer to store dict.Keys in a variable first, then loop through that key array while removing entries from the dictionary. This keeps the enumeration separate from the collection being changed.
Why does Scripting.Dictionary fail on Excel for Mac or the web?
Excel for the web does not execute VBA. The Windows Scripting Dictionary is a COM-based object and should not be assumed to be present in Mac Excel; use a compatible alternative if cross-platform support is required.
Related
- Excel Text to Columns: Split at Line Breaks
- Excel Text to Columns Greyed Out? Causes and Fixes
- Excel Text to Columns: How to Split Data
Try it: the Column Letter to Number: aA is 27, XFD is 16,384. Converts both ways.
More in VBA & macros
- Excel VBA Array Maximum Size: Limits and Practical TipsThere is no single, guaranteed maximum number of elements for an Excel VBA array: its usable size depends…
- Excel VBA Range.Formula: How to Read and Set FormulasIn Excel VBA, use a range’s Formula property to read or write a worksheet formula in A1 notation.…
- Tiny Macro Recorder: What TinyTask Can and Can’t DoIf you mean TinyTask, it is a small Windows macro recorder that captures mouse and keyboard actions and…
- Excel VBA: How to Change Cell Formats to GeneralTo change a cell’s number format to General with VBA, assign General to its NumberFormat property: Worksheets("Sheet1").Range("A1").NumberFormat =…
- Excel VBA: Get the Last Day of a MonthIn Excel VBA, use DateSerial(Year(d), Month(d) + 1, 0) to get the final date of the month containing…
- Excel VBA Boolean: True, False, and How to Use ThemYes—Excel VBA has a built-in Boolean data type for values that are either True or False. Declare a…
Sources: Function behaviour and menu paths: Microsoft Support documentation for Excel. Method & sources
Spotted an error in this guide? Let us know.