Find Unique Values in VBA: Methods and Examples

To find distinct values in VBA, read the source cells into a Scripting.Dictionary and add a value only when it is not already present. The Windows macro below scans A2:A on the active sheet, skips blanks and errors, and writes each value’s first occurrence to column C.

What “unique” means in Excel

People use “unique values” to mean two different things. Usually, they mean a distinct list: include each different value once, even if it appears several times in the source. For example, from Red, Blue, Red, Green, a distinct list is Red, Blue, Green.

Sometimes they mean values that occur exactly once. From that same source, the exactly-once result is Blue, Green; Red is excluded because it occurs twice. These are different tasks, so decide which result you need before writing the macro. The first example below produces distinct values, not values that occur exactly once.

There are also choices about whether capitalization, blank cells, spaces, and data types count as differences. A useful macro should make those rules clear rather than silently treating every cell the same way.

Use a dictionary to extract distinct values

A dictionary stores keys and can check quickly whether a key is already present. The macro uses each source value as a key. When it encounters a value for the first time, it adds the key and writes the original value to column C. Later occurrences are ignored, so the output stays in source order and retains the first spelling or value encountered.

This version uses late binding: CreateObject creates the dictionary without requiring you to add a reference in the VBA editor. It uses text comparison, so values such as Maple and maple are treated as the same text value. The code is for Excel on Windows.

Sub ExtractDistinctValues()
    Dim ws As Worksheet
    Dim lastRow As Long
    Dim outputRow As Long
    Dim sourceRange As Range
    Dim cell As Range
    Dim dict As Object
    Dim value As Variant

    Set ws = ActiveSheet
    lastRow = ws.Cells(ws.Rows.Count, "A").End(xlUp).Row

    ' The source data is expected to start in A2.
    If lastRow < 2 Then Exit Sub
    Set sourceRange = ws.Range("A2:A" & lastRow)

    Set dict = CreateObject("Scripting.Dictionary")
    dict.CompareMode = vbTextCompare
    outputRow = 2

    For Each cell In sourceRange.Cells
        value = cell.Value2

        ' Ignore error cells and blank values, including formula results of "".
        If Not IsError(value) Then
            If Len(CStr(value)) > 0 Then
                If Not dict.Exists(value) Then
                    dict.Add value, True
                    ws.Cells(outputRow, "C").Value = value
                    outputRow = outputRow + 1
                End If
            End If
        End If
    Next cell
End Sub

The macro assumes the list is in column A, starting at A2, and that column C is available for results beginning at C2. It does not clear old results or other cells in column C. Before running it again, clear the previous output yourself if the new list might be shorter; otherwise leftover values may look like part of the new result. The macro also leaves the source column unchanged.

Run the macro safely

In Excel for Microsoft 365 on Windows, save a copy of the workbook before running any macro. Macros cannot be undone with Ctrl+Z, so the copy gives you a way back if the output goes somewhere unexpected. Save the working file as an Excel Macro-Enabled Workbook (.xlsm) if you need to keep the macro in it.

Open the workbook and confirm that the source list is in A2 downward and column C is safe to use.
Press Alt+F11 to open the Visual Basic Editor. You can also open it from the Developer tab by choosing Visual Basic.
In the editor, choose Insert > Module, then paste the code into the module.
Return to Excel, click inside the macro, and press F5, or run it from Developer > Macros.

On a Mac, the Visual Basic Editor shortcut is commonly Option+F11; some keyboards require Fn as well. The Windows Scripting.Dictionary COM object used in this example is not a dependable cross-platform choice for Mac Excel. For a Mac workbook, use Excel’s UNIQUE worksheet function if available, or use a VBA approach based on arrays or a Collection instead. Macro security settings may also prevent a macro from running; only enable macros in workbooks you trust.

Change case, blank, and value rules

The comparison setting is important. dict.CompareMode = vbTextCompare makes dictionary comparisons between text keys case-insensitive. If capitalization should create separate entries, change it to vbBinaryCompare. Set the mode before adding any keys; changing it after the dictionary contains data can cause an error.

Blanks: The example skips empty cells and formulas that return an empty string. Remove the blank-check block if a blank should appear in the output.
Errors: The example skips cells containing errors such as #N/A. Remove the error check only if you have another deliberate way to handle error values.
Spaces: “North” and “North ” are different values because the second has a trailing space. If spaces are accidental, trim text before checking it, but be aware that trimming changes the source value used for comparison.
Numbers and text: A number stored as a number is not necessarily the same thing as the text string that looks like that number. Avoid converting every key with CStr unless you intentionally want values such as numeric 7 and text “7” to be treated alike.

The macro reads Value2, which returns the underlying cell value rather than a formatted display string. Thus, a date is handled as its underlying Excel value; the output cell’s formatting determines how it appears. If your requirement is to compare displayed text exactly, that is a different rule and should be implemented deliberately.

Return values that occur exactly once

To keep only values whose total count is one, count each value first, then make a second pass to write the values whose count is exactly one. This example uses the same source and output layout as the first macro, uses case-insensitive text comparison, and skips blanks and errors. As before, clear old output in column C yourself when needed.

Sub ExtractValuesThatOccurOnce()
    Dim ws As Worksheet
    Dim lastRow As Long
    Dim outputRow As Long
    Dim sourceRange As Range
    Dim cell As Range
    Dim dict As Object
    Dim value As Variant

    Set ws = ActiveSheet
    lastRow = ws.Cells(ws.Rows.Count, "A").End(xlUp).Row

    ' The source data is expected to start in A2.
    If lastRow < 2 Then Exit Sub
    Set sourceRange = ws.Range("A2:A" & lastRow)

    Set dict = CreateObject("Scripting.Dictionary")
    dict.CompareMode = vbTextCompare

    ' Count each nonblank, non-error value.
    For Each cell In sourceRange.Cells
        value = cell.Value2
        If Not IsError(value) Then
            If Len(CStr(value)) > 0 Then
                If dict.Exists(value) Then
                    dict(value) = dict(value) + 1
                Else
                    dict.Add value, 1
                End If
            End If
        End If
    Next cell

    ' Write values with a count of one, preserving source order.
    outputRow = 2
    For Each cell In sourceRange.Cells
        value = cell.Value2
        If Not IsError(value) Then
            If Len(CStr(value)) > 0 Then
                If dict(value) = 1 Then
                    ws.Cells(outputRow, "C").Value = value
                    outputRow = outputRow + 1
                End If
            End If
        End If
    Next cell
End Sub

For example, if A2:A6 contains Oak, Pine, Oak, Elm, Birch, the distinct-list macro returns Oak, Pine, Elm, Birch. The exactly-once macro returns Pine, Elm, Birch. The second pass is safe here because a value with a total count of one can only occur once in the source.

Choose between VBA, UNIQUE, and Advanced Filter

VBA is useful when the task is part of a repeatable process: for example, a button that prepares a report, combines data-cleaning steps, or writes results to a specific location. A dictionary is a practical choice for one-column lists because it combines duplicate checking with collection of the output.

If you only need a live distinct list in a worksheet, Excel’s UNIQUE function is often simpler. With source values in A2:A7, where the cells contain North, South, North, East, South, and West, enter =UNIQUE(A2:A7) in an empty cell. The result spills down as North, South, East, West. The function is available in Microsoft 365 and Excel 2021 or later; it recalculates as the source changes. It is also available in supported versions of Excel for Mac and Excel for the web. Unlike a macro, the formula does not require enabling VBA macros.

For a one-time operation, Excel’s built-in Advanced Filter may be enough. Select the list, choose Data > Sort & Filter > Advanced, select whether to filter the list in place or copy it elsewhere, and select Unique records only. Advanced Filter is a worksheet feature rather than a dynamic formula: run it again when you want to refresh the copied result. The dictionary macro is more adaptable when the unique list is only one step in a larger automated job.

Common problems and practical fixes

If the output is empty, confirm that the source really begins at A2. The code deliberately starts there, so a list in A1 or another column will not be read as intended. Also check whether the cells contain errors, blanks, or formulas returning empty strings, all of which the example skips.

If the macro reports that it cannot create the ActiveX object, the Windows Scripting Runtime object may be unavailable or restricted in that environment. This is another reason not to assume the dictionary version will work on Mac. A worksheet formula or a platform-appropriate VBA implementation is a better fit when the dictionary object cannot be created.

If values you expect to match appear separately, inspect capitalization, leading or trailing spaces, and whether some cells store numbers as text. If values you expect to remain separate are combined, check whether text comparison is appropriate for your data. Do not remove spaces or convert types automatically unless that matches the meaning of the data.

For very large lists, repeated worksheet reads and writes can slow a macro. A more advanced version can load the source range into a Variant array, process the array in memory, and write the final results to a range in one operation. That optimization adds code and is usually unnecessary for small or moderate lists; first make sure the simpler version gives the correct comparison behavior.

Frequently Asked Questions

How do I get unique values from a column in VBA?
Use a dictionary to test whether each cell value has appeared before. Add and output the value only on its first occurrence. The first macro above reads column A and writes the distinct list to column C.

How can I make VBA treat uppercase and lowercase as different?
Set the dictionary’s CompareMode to vbBinaryCompare before adding any keys. The example uses vbTextCompare, which treats text differing only by case as equivalent.

How do I find values that appear only once?
Count each value first, then output only values with a count of one. Use the second macro above; a normal distinct-value macro still includes values that were repeated in the source.

Can I find unique values without a macro?
Yes. In Microsoft 365 or Excel 2021 and later, enter =UNIQUE(A2:A7) in an empty cell, adjusting the range to fit your data. For a one-time result, Advanced Filter also has a Unique records only option.

Does the dictionary macro work in Excel for Mac?
The code shown relies on the Windows Scripting Runtime dictionary object, so do not assume it will run on Mac. Use the worksheet UNIQUE function if your Excel version supports it, or choose a Mac-compatible VBA implementation.

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.