To remove one entry from a VBA dictionary, call dict.Remove key, where key is the entry’s key—not its stored value or position. Check dict.Exists(key) first if the key might be missing; use dict.RemoveAll to clear every entry while keeping the dictionary object.
Basic syntax and a working example
A dictionary stores key–item pairs. The key identifies an entry, and the item is the value associated with it. The Remove method takes a key and deletes that pair. It does not take an item number, and it does not search through values for a match.
This short macro creates a late-bound dictionary, adds one pair, and removes it by key. The count printed in the Immediate window is 0 because the entry has been deleted.
Save a copy of your workbook before running a macro. VBA changes cannot be undone with Ctrl+Z, even if the macro affects workbook data.
Option Explicit
Sub RemoveOneDictionaryKey()
Dim dict As Object
Dim keyToRemove As String
' Create a Scripting.Dictionary on Windows.
Set dict = CreateObject("Scripting.Dictionary")
dict.Add "North", 12
keyToRemove = "North"
If dict.Exists(keyToRemove) Then
dict.Remove keyToRemove
End If
Debug.Print dict.Count ' Prints 0.
End Sub
In normal VBA, the method call is written without parentheses when it is used as a statement: dict.Remove keyToRemove. The object variable must already refer to a dictionary. If it is Nothing, the call cannot succeed.
Remove only when the key exists
Calling Remove for a key that is not in the dictionary raises a run-time error. That can happen when a key is optional, has already been deleted, or comes from a worksheet cell whose contents do not match the dictionary. Use the dictionary’s Exists method to test before removing:
If dict.Exists(keyToRemove) Then
dict.Remove keyToRemove
End If
Exists returns True if the dictionary currently contains that key and False otherwise. This makes the operation safe to repeat: after the first successful removal, a later attempt simply skips the call. It is generally clearer than using On Error Resume Next to ignore a missing-key error, because the existence check handles the expected case without suppressing unrelated errors.
For example, if keyToRemove contains an employee ID read from a cell, check it before removal rather than assuming the ID was loaded into the dictionary. If a missing key instead indicates a programming mistake, you may prefer to let the error occur or raise a deliberate error; the right choice depends on whether absence is normal in your procedure.
Remove an entry based on its value
A common question is how to remove a dictionary entry when you know its value but not its key. Remove cannot do that directly. You must inspect the key–item pairs, identify the key or keys whose items meet your condition, and then remove those keys.
When a loop may change the dictionary, work from a snapshot of its keys. The Keys method returns an array of keys; iterating that array avoids relying on a live dictionary enumeration while entries are being removed. This example removes every entry whose stored numeric value is zero:
Sub RemoveZeroValueEntries()
Dim dict As Object
Dim keys As Variant
Dim key As Variant
' Create sample key–value pairs.
Set dict = CreateObject("Scripting.Dictionary")
dict.Add "A", 0
dict.Add "B", 7
dict.Add "C", 0
If dict.Count > 0 Then
keys = dict.Keys
For Each key In keys
If dict(key) = 0 Then
dict.Remove key
End If
Next key
End If
Debug.Print dict.Count ' Prints 1; only key B remains.
End Sub
The condition in this example assumes the items are numeric. If items can be text, dates, errors, or object references, write a condition that handles those types appropriately. Also decide whether the rule should remove the first matching entry or every matching entry; the loop above deliberately removes all matches.
Remove one key, all entries, or the object?
These operations have different effects, so choose based on what you need to preserve:
dict.Remove keydeletes one key–item pair. Other entries remain available.dict.RemoveAlldeletes every pair from the dictionary. The dictionary object itself remains available for reuse.Set dict = Nothingreleases that variable’s reference to the object. It is not a selective deletion method, and other variables may still refer to the same object.
Use RemoveAll when you want to empty a dictionary before loading a new set of data. For example, a routine that reuses a module-level dictionary can clear it and then add fresh entries. Use Remove when the remaining entries still matter. Releasing an object reference is more relevant when the dictionary is no longer needed; it is not a substitute for clearing a shared dictionary that other code still uses.
Removing an entry also does not mean that an object stored as its item is necessarily destroyed at that moment. VBA objects can be referenced elsewhere. Removing the dictionary’s pair removes that reference held by the entry; the object remains alive while another reference to it exists.
Keys, matching, and CompareMode
Dictionary keys must be unique according to the dictionary’s comparison rules. For text keys, CompareMode controls whether differences in letter case count. With the default binary comparison, keys such as "North" and "north" are treated as different. With vbTextCompare, text matching is case-insensitive, so those spellings refer to the same key for dictionary operations.
If you need a particular text comparison, set the mode before adding entries. For example, assign dict.CompareMode = vbTextCompare after creating the empty dictionary and before calling Add. Changing CompareMode after entries have been added can raise an error. The same comparison rules matter to Exists and Remove, so a case difference may explain why a lookup behaves unexpectedly.
Be precise about whether your input is a key or an item. If a dictionary contains "North" as the key and 12 as the item, dict.Remove "North" removes the pair. dict.Remove 12 looks for a key of 12; it does not search for an item whose value is 12. To remove by item, use a loop such as the snapshot example above.
Creating the dictionary and platform notes
In Excel for Microsoft 365 on Windows, a common approach is late binding: declare the variable As Object and create it with CreateObject("Scripting.Dictionary"). This avoids selecting a reference in the VBA editor, and the sample macros use this approach. The Windows Scripting Runtime provides the dictionary object; it is not an Excel worksheet feature or a formula.
Early binding is another Windows option. In the VBA editor, choose Tools > References, select Microsoft Scripting Runtime, and declare a variable as Scripting.Dictionary. Early binding can provide type information and editor assistance, but a project using that reference depends on it being available on the computer where the project is opened. Late binding avoids a compile-time reference to that library, though the object still has to be available at run time.
Do not assume that CreateObject("Scripting.Dictionary") is portable to every Excel platform. Office for Mac does not provide the same Windows COM environment, and Excel for the web does not run VBA macros. If a solution must work on those platforms, consider a VBA Collection where its capabilities are sufficient, or use a platform-appropriate alternative such as Office Scripts in Excel for the web. Those alternatives have different interfaces; they do not support this exact Remove call.
Common mistakes and troubleshooting
- Passing an item instead of a key:
Removesearches by key. Find the matching key first if you only know the stored value. - Removing a missing key: Test with
Existswhen absence is possible, or investigate why the key was not added. - Assuming text keys always ignore case: Check
CompareModeand make sure it was set before entries were added. - Changing the dictionary during enumeration: Use a keys snapshot when deleting entries as part of a loop, rather than depending on how a live enumeration responds to changes.
- Expecting deletion to change a worksheet: A dictionary is an in-memory VBA object. Removing an entry does not delete a row or clear a cell unless your code separately changes the worksheet.
- Expecting Ctrl+Z to restore data: Excel’s Undo command does not reverse changes made by VBA. Save a backup before running a macro that changes important workbook content.
If a removal seems to leave a record behind, inspect the keys with the debugger or print them to the Immediate window. Check for leading or trailing spaces, different data types, and case differences. A key that looks identical on a worksheet may contain an extra space, while a dictionary key is the value supplied to it—not necessarily the displayed text you expect.
Frequently Asked Questions
How do I remove a key from a VBA dictionary?
Use dict.Remove key. If the key may not exist, first test dict.Exists(key).
How do I delete a dictionary item by its value?
Loop through the keys, compare each item with the value or condition you want, and call Remove using the matching key. Use a snapshot from dict.Keys if the loop deletes entries.
What is the difference between Remove and RemoveAll?Remove deletes one pair identified by its key. RemoveAll empties the dictionary but leaves the dictionary object available for reuse.
Can I remove an item while using For Each?
Avoid changing the dictionary while directly enumerating it. Take a keys snapshot with dict.Keys, then loop over that array and remove matching keys from the dictionary.
Does removing an entry delete a worksheet row?
No. A dictionary is separate from the worksheet grid. To delete a row, your macro must also use a worksheet row-deletion operation.
Related
- Data Validation Checks in Excel: Rules and Examples
- Data Validation Checklist for Excel: What to Check
- Conditional Formatting in Microsoft Project: What Works
Try it: the Column Letter to Number: aA is 27, XFD is 16,384. Converts both ways.
More in VBA & macros
- VBA AutoFilter Delete: Remove Visible Rows Safely in ExcelYes. In Excel VBA, use SpecialCells(xlCellTypeVisible) on the filtered data rows, then delete those rows; leave the header…
- VBA AutoFilter Operator: Values, Uses, and ExamplesIn VBA, the Operator argument of Range.AutoFilter tells Excel how to combine two criteria for one field or…
- VBA Get Character From String: Mid, Loops & TipsTo get one character from a VBA string by position, use Mid$(text, position, 1). For example, Mid$("Excel VBA",…
- Make a Plot with a Dictionary in VBA: Excel GuideYes. In Excel for Microsoft 365 on Windows, use a VBA Scripting.Dictionary to group records by category, write…
- How to Copy and Paste VBA Code into ExcelTo copy and paste VBA code into Excel, open the Visual Basic Editor, insert a standard module, paste…
- Excel Sheet Open VBA: Open and Activate a WorksheetTo open a worksheet that is already in an Excel workbook, use VBA to activate it: ThisWorkbook.Worksheets("Summary").Activate. If…
Sources: Function behaviour and menu paths: Microsoft Support documentation for Excel. Method & sources
Spotted an error in this guide? Let us know.