Excel VBA: Find Unique Entries in a Column

To find distinct entries in an Excel column with VBA, loop through the data, add each value only once to a dictionary, and write the results to another column. The macro below reads column A from row 2, skips blanks and errors, and writes the first occurrence of each value to column B.

What “unique” means in Excel

People use “unique” to mean two different things. A distinct list contains one copy of every value that appears in the source, even if that value occurs many times. A list of values that occur exactly once contains only entries whose frequency in the source is one. The macro here produces the first kind: a de-duplicated list.

For example, if A2:A7 contains Apple, Pear, Apple, Plum, Pear, and Grape, the macro returns Apple, Pear, Plum, and Grape, in the order they first appeared. It does not return only Plum and Grape. That distinction matters when preparing names, categories, product codes, or other lists for review.

The example assumes row 1 is a header, source data is in column A, and column B is reserved for results. It treats text without regard to capitalization, so “apple” and “Apple” count as the same entry. It does not trim spaces, so “Apple” and “Apple ” (with a trailing space) are different.

VBA macro to list distinct values

This Windows desktop macro uses a late-bound Scripting.Dictionary, so you do not need to add a reference in the VBA editor. It keeps the first value encountered for each key and writes results beneath a header in column B. Before running it, save a copy of the workbook: macros cannot be undone with Ctrl+Z.

Sub ListUniqueValuesInColumnA()
    Dim ws As Worksheet
    Dim lastSourceRow As Long
    Dim lastOutputRow As Long
    Dim clearToRow As Long
    Dim cell As Range
    Dim dict As Object
    Dim v As Variant
    Dim key As String
    Dim keys As Variant
    Dim items As Variant
    Dim results() As Variant
    Dim i As Long

    Set ws = ActiveSheet

    ' Find the last used row in the source and reserved output columns.
    lastSourceRow = ws.Cells(ws.Rows.Count, "A").End(xlUp).Row
    lastOutputRow = ws.Cells(ws.Rows.Count, "B").End(xlUp).Row
    clearToRow = Application.Max(lastSourceRow, lastOutputRow)

    ' Clear old results in column B, leaving its header intact.
    If clearToRow >= 2 Then ws.Range("B2:B" & clearToRow).ClearContents
    If lastSourceRow < 2 Then Exit Sub

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

    ' Keep the first occurrence; ignore blank cells and error values.
    For Each cell In ws.Range("A2:A" & lastSourceRow).Cells
        v = cell.Value2
        If Not IsError(v) Then
            If Len(CStr(v)) > 0 Then
                key = CStr(v)
                If Not dict.Exists(key) Then dict.Add key, v
            End If
        End If
    Next cell

    If dict.Count = 0 Then Exit Sub

    ' Write the collected values in one operation.
    keys = dict.Keys
    items = dict.Items
    ReDim results(1 To dict.Count, 1 To 1)
    For i = 0 To dict.Count - 1
        results(i + 1, 1) = items(i)
    Next i
    ws.Range("B2").Resize(dict.Count, 1).Value = results
End Sub

The macro clears column B from row 2 through the last used row found in either A or B. That makes rerunning it less likely to leave old results beneath a shorter new list, but it also means B2 downward must be reserved for this output. Put a heading such as “Unique values” in B1 if you want one; the macro leaves row 1 alone.

How the dictionary approach works

A dictionary stores keys and associated values. Before adding a source entry, the code asks whether its key already exists. If not, it adds the key and the original value. If it does exist, the later occurrence is skipped. Because the loop moves from the top of the source to the bottom, the output follows first-appearance order rather than alphabetical order.

The key is made with CStr, which converts the cell value to text for comparison. The dictionary’s vbTextCompare setting makes text comparison case-insensitive. Since keys are text, a numeric 1 and text “1” can be treated as the same key; the output keeps the value from the first occurrence. If preserving distinctions between data types is essential, use a different key-building rule rather than converting every value to text.

Blank cells and cells containing formulas that return an empty string are skipped because their displayed value has zero length. Cells containing Excel errors are also skipped, avoiding a conversion error when the macro reads them. A cell containing spaces is not blank to this test. If you want whitespace-only cells ignored or want leading and trailing spaces removed, adjust the test deliberately; trimming can also make otherwise different entries collapse into one.

Run the macro in Excel for Windows

Make a backup copy of the workbook, then open the copy in desktop Excel. VBA does not run in Excel for the web.
Save the workbook as an Excel Macro-Enabled Workbook (*.xlsm) if it is not already in that format.
Press Alt+F11 to open the Visual Basic Editor. On a Mac, the editor shortcut is commonly Option+F11; some keyboards also require Fn.
In the editor, choose Insert > Module, paste the procedure into the module, and return to Excel.
Select the worksheet containing the source list, then run the macro with Alt+F8, select ListUniqueValuesInColumnA, and choose Run. You can also run it from the VBA editor with the cursor inside the procedure and F5.

Macros may be blocked by your organization’s security settings. Only run code you understand and trust; do not lower macro security broadly just to run a copied procedure. If the workbook is saved as .xlsx, VBA code will not be retained in that file format, so use .xlsm for a workbook that needs to keep the macro.

Adapt the macro to another column or output location

For source data in column C, change the source references from “A” to “C” in the last-row calculation and the range loop. To put results in column D, change the output references from “B” to “D” consistently, including the output last-row calculation and the range to clear. Keep the output column separate from the source so clearing old results cannot erase the list being processed.

If the source has no header and begins in A1, change the starting row from 2 to 1 in the range and clearing logic. If the list is on a known worksheet rather than whichever sheet is active, replace Set ws = ActiveSheet with a worksheet reference such as Set ws = ThisWorkbook.Worksheets(“Data”). The sheet name must match the workbook exactly.

To make comparison case-sensitive, set the dictionary’s CompareMode to vbBinaryCompare instead of vbTextCompare. Set CompareMode before adding any keys. For a rule that ignores surrounding spaces, assign a trimmed string to key while keeping the original value for output. Be aware that normalizing case or spaces changes what counts as a duplicate, so use a rule that matches the data’s meaning.

Common problems and limitations

Results appear in the wrong sheet: the sample uses ActiveSheet. Select the intended worksheet before running, or refer to a named worksheet in the code.
Old output remains: the macro clears column B only through the last used row it detects in A or B. Confirm that B is reserved and that the source begins where the code expects.
Dates display as numbers: the macro reads values with Value2 and writes them into a general-formatted destination. If the results are date serials, apply an appropriate date number format to column B.
Similar-looking entries stay separate: hidden spaces, nonbreaking spaces, punctuation, or different spellings can make text values distinct. Inspect or clean the source if those differences are accidental.
The macro fails on a Mac: this example creates a Windows Scripting.Dictionary object, which relies on Windows COM support and is not a portable Mac solution. Use Excel’s UNIQUE function where available, or rewrite the procedure using a Mac-compatible collection approach.

The macro also reads only the active sheet’s column A and assumes the list is contiguous enough for the last-used-row method to cover it. A blank in the middle does not stop the loop, but data below the final nonblank cell cannot be detected as source data. For large or complex workbooks, make the worksheet and source range explicit.

VBA compared with Excel’s built-in options

If you have Microsoft 365 or Excel 2021, a formula may be simpler than a macro. With source values in A2:A100, enter =UNIQUE(A2:A100) in an empty cell outside the source. Excel spills a distinct list into the cells below. For the example Apple, Pear, Apple, Plum, Pear, Grape, the spilled result is Apple, Pear, Plum, Grape. The formula is dynamic: changes to the source can update the result, while the VBA output is a snapshot until you run the macro again.

Another built-in option is Data > Remove Duplicates. It can remove repeated rows from a selected range, but it changes the selected data in place; copy the data first if you need to preserve the original list. It is useful for a one-time cleanup, whereas a macro is useful when you repeat the same extraction or need a consistent custom rule.

Use VBA when the process must be repeatable, when you need to apply a particular comparison rule, or when a larger workflow already uses macros. Use UNIQUE for a live formula result in a supported desktop version, and use Remove Duplicates for an intentional one-time edit. Excel for the web can display a workbook containing VBA but cannot run the macro; open it in desktop Excel to execute VBA.

Frequently Asked Questions

How do I find values that appear only once, not a distinct list?
The sample macro keeps one copy of every value, including values repeated in the source. To find values whose count is exactly one, first count each key’s occurrences and then output only keys with a count of one. That is a different requirement from removing duplicates, so the macro must be changed rather than simply rerun.

Does this macro keep the first occurrence or the last?
It keeps the first occurrence because it scans from the top down and adds a key only when it is not already present. Later copies do not replace the stored value. Sorting the source first changes which occurrence comes first.

Can I make the results alphabetical?
The sample preserves the source order and does not sort. You can sort the output column after running the macro, or use a separate sorting step in VBA. Sorting is a separate choice from deduplication and may not be appropriate when the original order carries meaning.

Will it work in Excel for Mac or Excel for the web?
Excel for the web does not run VBA. The shown dictionary-based code targets Excel for Windows and may fail on Mac because it creates a Windows COM object. On Mac, use UNIQUE if your Excel version supports it, or use a macro written with Mac-compatible VBA objects.

Why are values with different capitalization combined?
The code sets CompareMode to vbTextCompare, so text keys are compared without case sensitivity. Change it to vbBinaryCompare before any keys are added if “Pear” and “pear” must be separate entries. Remember that spaces and other characters still affect the comparison unless you normalize them too.

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.