Filter a Dictionary in Excel VBA: Practical Examples

Excel VBA has no built-in Filter method for a Scripting.Dictionary. The usual approach is to loop through the dictionary’s keys, test each key or value, and copy matching entries into a new dictionary; this keeps the original intact and gives you a reusable filtered result.

What “filter a dictionary” means

A VBA dictionary stores unique keys and their associated items. For example, an order number can be a key and its region can be the item: Order-101 → North. Filtering means selecting some of those key-item pairs according to a rule, such as keeping only entries whose region is North.

There is no dictionary-specific equivalent of a worksheet filter button. A dictionary is an object in VBA, not a range of cells, so filtering it requires code that checks each entry. The result can be another dictionary, an array, or rows written to a worksheet. The right destination depends on what the next step in your macro needs to do.

In this example, the values are text. A filter for numeric ranges, dates, or multiple conditions uses the same loop structure but changes the test. Dictionary keys must be unique; if the source already has two rows with the same key, one cannot preserve both as separate dictionary entries.

How the filtering loop works

The basic process is straightforward: create an empty destination dictionary, visit each key in the source, inspect the associated item, and add the pair to the destination only when it meets the condition. The source is read but not changed.

Choose whether the rule applies to a key, an item, or both.
Create the result dictionary and set its key-comparison behavior before adding entries.
Loop over source.Keys, test each entry, and add matches.
Use the result in later code or write it to a worksheet.

Looping through keys is useful because each key gives you access to its item with source(key). The dictionary’s Keys property returns the keys as an array. For a modest dictionary, this is an easy pattern to read and maintain. For a very large dataset, benchmark the complete workflow rather than assuming the dictionary loop is the only performance cost.

Example: keep entries with a matching value

The function below returns a new dictionary containing entries whose text values equal the requested text, without regard to letter case. It preserves the source dictionary’s key comparison mode. It assumes that the source values are scalar text values; adapt the condition for numbers, dates, errors, or other data types.

Public Function FilterDictionaryByValue(ByVal source As Object, _
                                        ByVal wanted As String) As Object
    Dim result As Object
    Dim key As Variant

    ' Create a new dictionary and preserve the source key comparison mode.
    Set result = CreateObject("Scripting.Dictionary")
    result.CompareMode = source.CompareMode

    ' Copy only entries whose text value matches the requested text.
    For Each key In source.Keys
        If StrComp(CStr(source(key)), wanted, vbTextCompare) = 0 Then
            result.Add key, source(key)
        End If
    Next key

    Set FilterDictionaryByValue = result
End Function

Public Sub ExampleFilterDictionary()
    Dim source As Object
    Dim matches As Object
    Dim key As Variant
    Dim rowNumber As Long

    ' Set comparison mode before adding keys.
    Set source = CreateObject("Scripting.Dictionary")
    source.CompareMode = vbTextCompare
    source.Add "Order-101", "North"
    source.Add "Order-102", "South"
    source.Add "Order-103", "North"

    Set matches = FilterDictionaryByValue(source, "North")

    ' Write results to columns A and B on the active worksheet.
    With ActiveSheet
        .Cells(1, 1).Value = "Order"
        .Cells(1, 2).Value = "Region"
        rowNumber = 2
        For Each key In matches.Keys
            .Cells(rowNumber, 1).Value = key
            .Cells(rowNumber, 2).Value = matches(key)
            rowNumber = rowNumber + 1
        Next key
    End With
End Sub

With the three sample entries shown, the result contains two pairs: Order-101 with North, and Order-103 with North. The macro writes those two rows below the headers on the active sheet. Run it with a blank output area selected; the code does not clear old cells below the new results.

In the Visual Basic Editor, place both procedures in a standard module, such as one added with Insert > Module. The example uses late binding, so it does not require selecting a reference to Microsoft Scripting Runtime in Tools > References. Late binding avoids that reference setting, but it does not make the Windows COM object available on every platform.

Filter by a key, prefix, or more than one condition

To filter by key instead of value, change the condition inside the loop to inspect CStr(key). For example, a case-insensitive test for keys containing “Order” can use InStr(1, CStr(key), "Order", vbTextCompare) > 0. Add the same key and its item when that condition is true. A prefix test can use Left$(CStr(key), Len(prefix)) = prefix; use a case-insensitive comparison if capitalization should not matter.

For multiple criteria, combine tests in the If condition. For instance, keep an entry only when its region is North and its key begins with a chosen prefix. If values are numbers or dates, compare them as their real types rather than converting every value to text. Text conversion can produce misleading comparisons, particularly for dates and numeric-looking strings.

If a dictionary item is itself an object or an array, the sample’s CStr conversion is not suitable. Test the relevant property of the object or element of the array instead. Decide how the function should treat empty values and Null before using it on inconsistent data, since blindly converting such values can cause errors or unintended matches.

Dictionary filtering versus Excel AutoFilter

Use a dictionary loop when the data already lives in a dictionary, when you need a result for more VBA processing, or when the entries are being assembled in memory. Use worksheet AutoFilter when the data is in a tabular range and the goal is to show or hide rows for a person working in the sheet. These tools solve related but different problems: AutoFilter changes the visible rows in a range; it does not return a filtered dictionary.

Excel’s worksheet FILTER function is another separate feature. In Microsoft 365 and Excel 2021 or later, it can return matching rows from a range as a dynamic array. It does not directly accept a Scripting.Dictionary object as its source. If your data begins in cells, a worksheet formula or AutoFilter may be simpler than loading it into a dictionary. If your macro already uses a dictionary for lookups, filtering the object in VBA can avoid converting back and forth unnecessarily.

Performance and memory choices

Building a second dictionary uses additional memory because matching keys and items are stored in another object. That is usually a sensible trade-off: the source remains unchanged, the result is easy to reuse, and a later step can still access entries by key. If you only need to display results once, writing matches directly to a worksheet or an output array may be more appropriate.

Removing nonmatching entries from the source can avoid a second dictionary, but it changes the input and makes the code more delicate. Do not remove keys while iterating over the same dictionary’s keys unless the iteration strategy is designed for mutation; changing a collection during traversal can lead to skipped entries or errors. A safer in-place approach is to collect unwanted keys first, then remove them in a separate pass. Keep a copy if later code needs the unfiltered data.

For large tables, repeated worksheet-cell reads inside a loop can be slower than reading a range into a VBA array once, processing the array, and writing the result back in a block. That is a worksheet I/O issue rather than a special dictionary rule. Measure with realistic data, and keep the clearest approach that meets the workbook’s performance needs.

Common errors and practical safeguards

Duplicate keys: Dictionary.Add raises an error if the key already exists under the dictionary’s comparison rules. A dictionary cannot represent repeated keys as separate records. Use a different unique key or store multiple records in an item collection.
Comparison mode: Set CompareMode before adding keys. It controls whether keys differing only by letter case are treated as the same. The sample sets the result mode to match the source.
Unexpected value types: The sample compares text. Add explicit handling for empty values, Null, errors, or non-text items if they can occur in your data.
Wrong output sheet: The example writes to ActiveSheet. Select the intended sheet first or change the code to refer to a named worksheet, so results do not land on an unrelated tab.
Macro security and undo: Save the workbook as a macro-enabled .xlsm file if it needs to retain VBA, and enable macros only in files you trust. Save a copy before running a macro: VBA changes cannot be undone with Ctrl+Z.

For a reusable production macro, also decide what should happen if the source object is missing or contains no entries. An empty source naturally produces an empty result in this pattern, but a missing object is a different situation and should be checked before reading its properties. Clear error messages are preferable to silently returning incomplete output.

Windows, Mac, and Excel for the web

This example is written for Excel for Microsoft 365 on Windows. CreateObject("Scripting.Dictionary") relies on a Windows scripting component. Late binding removes the need for a checked VBA reference, but it does not guarantee that the component exists on a Mac. On Mac, check whether the required object is available in the Excel installation you use; otherwise, use a VBA Collection, arrays, or worksheet-based filtering and adapt the lookup logic.

Excel for the web can open a workbook containing VBA, but it cannot run VBA macros. Run this code in the desktop Excel application. On Windows, Alt+F11 opens the Visual Basic Editor; on Mac, the editor shortcut and availability can differ by keyboard and Excel settings, so use the Developer tab or Excel’s Tools > Macro > Visual Basic Editor menu if needed. The code itself does not use a keyboard shortcut for filtering.

Frequently Asked Questions

Does a VBA dictionary have a Filter method?
No. A Scripting.Dictionary has no built-in method that returns entries matching a condition. Loop through its keys and copy matches to a new dictionary, or send the matches to another data structure.

Can I filter a dictionary by its key?
Yes. In the loop, test the key variable rather than source(key). You can compare the whole key, check a prefix, or search for text within it, depending on the rule.

Does the example change the original dictionary?
No. It creates and returns a separate dictionary. That preserves the source for other procedures and avoids modifying a collection while iterating over it.

Can I filter dictionary entries by a number or date?
Yes. Keep the same loop and compare the item using the appropriate numeric or date condition. Avoid converting typed values to text unless a text comparison is specifically what you want.

Why does CreateObject fail on my Mac or in Excel for the web?
The example depends on a Windows scripting object and runs only in desktop Excel. Excel for the web does not run VBA, and Mac availability of that object is not guaranteed. Use a compatible alternative such as arrays, a collection, or worksheet filtering.

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.