Filter Dictionary Keys in Excel VBA: Code and Options

Yes. In Excel VBA, filter a Scripting.Dictionary by looping through its keys and copying the matching key/value pairs into a new dictionary; the object has no built-in FilterKeys method. The example below keeps keys whose text contains a search term, while leaving the original dictionary unchanged.

What a VBA dictionary contains

A dictionary stores items as key/value pairs. Each key identifies one item and must be unique under the dictionary’s comparison rules; the associated value can be a number, text, date, object, or another kind of value. For example, a sales dictionary might use region names such as North and South as keys, with sales totals as values.

In VBA, dictionaries are commonly created with the Scripting.Dictionary object. On Windows, you can create one with late binding using CreateObject("Scripting.Dictionary"), or use early binding by enabling the Microsoft Scripting Runtime reference in the Visual Basic Editor. Late binding avoids setting a reference, which can make a workbook easier to share between Windows computers with different reference configurations.

Filtering keys means deciding which keys meet a rule and retaining their associated values. It does not mean changing the dictionary’s keys in place. A separate result dictionary is generally safer: the source remains available, and the result contains only the selected entries.

How key filtering works

The basic process is straightforward: visit each key, test it, and copy the key and its value when the test is true. In the example, InStr checks whether the key contains the search text. Its vbTextCompare argument makes that text search case-insensitive, so a search for “north” can match “North”.

Create an empty result dictionary.
Set its comparison mode to match the source dictionary.
Loop through the source’s keys and apply a condition to each one.
Add each matching key and its existing value to the result.
Return or use the result without altering the source.

For a dictionary with n entries, this approach checks each entry once, so the work grows roughly in proportion to the number of entries. It also stores a second dictionary containing the matches. That extra memory is usually a reasonable trade-off for keeping the original data intact.

Example: keep keys containing text

This function accepts a dictionary and a search term, then returns a new dictionary with the matching entries. It assumes the keys can be converted to text. If the keys are numeric, the search is performed on their text representation, not as a numeric comparison.

Public Function FilterDictionaryKeys(ByVal source As Object, _
                                     ByVal searchText As String) As Object
    Dim filtered As Object
    Dim key As Variant

    ' Create a separate result dictionary.
    Set filtered = CreateObject("Scripting.Dictionary")
    filtered.CompareMode = source.CompareMode

    ' Keep each key whose text contains the search term.
    For Each key In source.Keys
        If InStr(1, CStr(key), searchText, vbTextCompare) > 0 Then
            filtered.Add key, source.Item(key)
        End If
    Next key

    Set FilterDictionaryKeys = filtered
End Function

For example, if the source has the keys North, South, and West and you search for “th”, the returned dictionary contains North and South, together with their original values. West is omitted. Searching for an empty string matches every key because an empty search term is found at the start of each string; add a separate check if an empty term should instead return no results.

In the Visual Basic Editor on Windows, press Alt+F11 to open the editor, then place the function in a standard module. On Mac, Option+F11 commonly opens the editor; some keyboards require Fn as well. Save a copy of the workbook before running any macro: VBA changes cannot be undone with Ctrl+Z. This function itself builds an in-memory result, but a calling macro may also change worksheet cells or other workbook content.

Change the matching rule

“Filter keys” can describe several different tests. The example uses a contains test, but the loop stays the same when the rule changes. Choose the condition that matches the actual question you want to answer:

Contains: Keep a key when a word or character sequence appears anywhere in it. This is what the example’s InStr condition does.
Starts with: Compare the first characters of a text key with a prefix. For predictable case-insensitive matching, use a text comparison such as StrComp on the relevant portions rather than relying on the module’s Option Compare setting.
Exact match: Compare the full key to the requested text. Whether the dictionary itself treats differently cased text keys as equivalent depends on its CompareMode.
Pattern match: VBA’s Like operator supports patterns, but wildcard characters have special meanings. It is useful for deliberate patterns, not for a literal search term that might contain wildcard characters.
Numeric range: If keys are numbers, compare them numerically instead of converting them with CStr. A text search for “1” could also match the text representation of 10 or 21, which is not the same as testing whether a number falls in a range.

The search comparison and dictionary comparison are separate decisions. The example explicitly asks InStr to ignore text case, while copying the source dictionary’s CompareMode ensures the result uses the same key-comparison setting for later additions and lookups. Set a dictionary’s comparison mode before adding items; changing it after keys have been inserted can cause an error.

Filtering keys versus filtering values

A common source of confusion is whether the rule applies to the key or the value. If keys are product codes and values are stock counts, searching the key for a prefix such as “AB” selects product codes. It does not select items based on whether their stock count is below a threshold.

For a value-based rule, keep the same loop but test source.Item(key) instead of the key. For instance, a numeric value condition could retain entries whose stock count is less than a chosen limit. Check that the values really are numeric before using numeric comparisons: dictionary values are Variants and could contain text, an error value, or an object. If the rule depends on both fields, combine the key and value tests in the same condition.

Another related distinction: filtering a dictionary does not filter worksheet rows automatically. The function returns a VBA object in memory. Writing its results to a worksheet requires a separate step to place the keys and values in cells.

Common problems and how to avoid them

“ActiveX component can’t create object.” This can happen when Scripting.Dictionary is unavailable in the environment, including many Mac setups, or when the object cannot be created. The Windows late-binding example is not a guarantee of cross-platform support.
Duplicate-key error. A dictionary cannot hold duplicate keys under its comparison rules. If the source is a valid dictionary and each key is copied only once, the filter should not create duplicates. A case-insensitive comparison mode can make keys that differ only by letter case count as equivalent.
Unexpected case matching. The example’s text search is case-insensitive because it uses vbTextCompare. If case should matter, use a binary comparison for the search. Do not assume that the dictionary’s CompareMode controls every text test in the procedure.
Type mismatch or conversion issues. CStr makes the sample convenient for text-like keys, but a numeric key is converted to text and then searched as text. Use a numeric condition for numeric keys, and avoid passing values that cannot safely be converted to strings.
No results. A valid search can return an empty dictionary. Check its Count before trying to retrieve a particular key; looking up a key that is not present can raise an error.
Changing the source while looping. The function only reads the source and writes to another object. That separation avoids the complications of adding or removing entries during iteration.

Returning results to an Excel worksheet

If the goal is to show matching entries in cells, the filtered dictionary is an intermediate result rather than the final display. A calling procedure can loop over the result’s keys and write each key and corresponding value into two worksheet columns. Decide where output should go first, and clear only the intended output area if old results must be removed.

Do not assume that a dictionary’s keys are sorted. If the output must be alphabetical or follow a numeric order, collect the keys into an array and sort that array before writing. Likewise, if the dictionary was built from a range, retaining the original row number as part of the value can help connect a filtered entry back to its source row. Filtering the dictionary alone does not preserve worksheet formatting, formulas, or row relationships unless you explicitly store and reproduce them.

Dictionary filtering compared with other options

A dictionary is useful when keys are unique and you need fast lookups by key, or when your VBA code already has data in key/value form. For a one-off filter of a normal worksheet table, Excel’s built-in AutoFilter may be simpler because it filters visible rows directly and preserves the table layout. In Microsoft 365, the worksheet FILTER function can also return rows that meet criteria, but it works on worksheet arrays and ranges, not as a method on a VBA dictionary.

For small collections, a VBA Collection or a two-column array may be adequate, but neither is a direct substitute for dictionary key lookup. If the data is already in a range and the main goal is analysis, a table, AutoFilter, or worksheet formula may avoid writing and maintaining a macro. Choose a dictionary when its unique-key lookup behavior is useful, not merely because the word “filter” appears in the task.

On Mac, test dictionary creation in the version and environment where the workbook will run. If CreateObject("Scripting.Dictionary") is unsupported there, consider a VBA array or a custom class-based structure, or keep the filtering in worksheet ranges. Excel for the web does not run VBA macros; use desktop Excel to execute this code.

Frequently Asked Questions

Does VBA have a FilterKeys method for dictionaries?
No. A Scripting.Dictionary does not provide a built-in method specifically for filtering keys. Loop through Keys, test each key, and copy matches into another dictionary or an array.

Can I filter a dictionary without creating a second one?
You can remove nonmatching items, but modifying a collection while iterating through its keys can complicate the loop and risks skipping entries. Building a separate result is clearer and preserves the original.

How do I filter keys that start with a specific prefix?
Change the test from a contains search to a prefix comparison. Compare the first characters of each text key with the prefix, and choose explicitly whether the comparison should be case-sensitive.

Can I filter dictionary items by their values?
Yes. Iterate through the keys as before, but apply the condition to source.Item(key). Validate the value’s type first if the rule requires numeric or date comparisons.

Will this code work in Excel for Mac or Excel for the web?
Do not assume that the Windows Scripting.Dictionary creation method works on Mac; test it or use another data structure. Excel for the web does not execute VBA, so run the macro in desktop Excel.

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.