Excel VBA: Find Unique Values in a Column

To find unique values in an Excel column with VBA, loop through the source cells, keep the first occurrence of each value, and write the results to a separate worksheet. The macro below does this for column A, skips blank cells and errors, and preserves the order in which values first appear.

What the macro does

The example is designed for Excel for Microsoft 365 on Windows. It treats row 1 as a heading and reads data from A2 down to the last non-empty cell in column A. It creates a worksheet named Unique Results if needed, then puts the unique values in that sheet’s column A beneath a heading.

Only the first occurrence of a value is included, so the output follows the source order.
Empty cells and formulas that return an empty string are ignored.
Excel error values, such as #N/A, are ignored rather than copied.
Text comparisons are case-insensitive: North and north count as the same value.
Text and numbers remain distinct: the number 12 and the text value 12 are treated as different values.

Before running any macro, save a copy of the workbook. VBA macros cannot be undone with Ctrl+Z, and this macro clears column A on the worksheet named Unique Results each time it runs. Keep that sheet for results only, and run the macro while the worksheet containing your source data is active.

VBA code to find unique values

Paste this procedure into a standard VBA module. It uses a late-bound Windows scripting dictionary to check whether each value has already appeared. The separate output array lets the macro write results to the worksheet in a block instead of making a worksheet write for every unique cell.

Sub FindUniqueValuesInColumn()
    Dim sourceWs As Worksheet
    Dim resultWs As Worksheet
    Dim wb As Workbook
    Dim dict As Object
    Dim data As Variant
    Dim output() As Variant
    Dim lastRow As Long
    Dim rowCount As Long
    Dim uniqueCount As Long
    Dim i As Long
    Dim v As Variant
    Dim key As String

    ' Use column A on the active worksheet; row 1 is a header.
    Set sourceWs = ActiveSheet
    Set wb = sourceWs.Parent
    lastRow = sourceWs.Cells(sourceWs.Rows.Count, "A").End(xlUp).Row

    ' Find or create the dedicated results worksheet.
    On Error Resume Next
    Set resultWs = wb.Worksheets("Unique Results")
    On Error GoTo 0

    If resultWs Is Nothing Then
        Set resultWs = wb.Worksheets.Add(After:=wb.Worksheets(wb.Worksheets.Count))
        resultWs.Name = "Unique Results"
    End If

    ' This clears column A on the results sheet.
    resultWs.Columns("A").ClearContents
    resultWs.Range("A1").Value = "Unique values"

    If lastRow < 2 Then Exit Sub

    data = sourceWs.Range("A2:A" & lastRow).Value2
    rowCount = UBound(data, 1)
    ReDim output(1 To rowCount, 1 To 1)

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

    For i = 1 To rowCount
        v = data(i, 1)

        ' Skip worksheet errors, genuinely empty cells, and empty strings.
        If Not IsError(v) Then
            If Not IsEmpty(v) Then
                If Not (VarType(v) = vbString And Len(v) = 0) Then
                    ' Include the value type so text "12" differs from number 12.
                    key = CStr(VarType(v)) & "|" & CStr(v)

                    If Not dict.Exists(key) Then
                        dict.Add key, True
                        uniqueCount = uniqueCount + 1
                        output(uniqueCount, 1) = v
                    End If
                End If
            End If
        End If
    Next i

    If uniqueCount > 0 Then
        resultWs.Range("A2").Resize(rowCount, 1).Value = output

        ' Remove unused rows from the output block, if there were duplicates.
        If uniqueCount < rowCount Then
            resultWs.Range("A" & (uniqueCount + 2) & ":A" & (rowCount + 1)).ClearContents
        End If
    End If
End Sub

How to add and run the macro

Save the workbook as an Excel Macro-Enabled Workbook (.xlsm) so it can retain VBA code.
Activate the sheet with the source values in column A. Put a heading in A1 if you want to use the code unchanged.
Press Alt+F11 on Windows to open the Visual Basic Editor. Choose Insert > Module, then paste the procedure into the module window.
Return to Excel and run FindUniqueValuesInColumn from Developer > Macros, or press Alt+F8, select the macro, and choose Run.
Open the Unique Results worksheet to see the output. Running the macro again replaces the existing results in that sheet’s column A.

If the Developer tab is not visible, enable it through File > Options > Customize Ribbon on Windows. The Visual Basic Editor shortcut on a Mac is generally Option+F11; some keyboards require holding Fn as well. The Windows dictionary object used in this specific macro is not a dependable cross-platform choice for Mac, so use a formula or Advanced Filter there, or adapt the VBA to use a comparison loop.

Adjust the input column and header row

The input range is controlled by the references to column A and the starting row. To read column D instead, change both occurrences of "A" used to find the last row and build the input range to "D". The output remains on the results sheet in column A.

The procedure assumes row 1 contains a header. If your data starts in A1 with no header, change the range that begins at A2 so it begins at A1, and adjust the last-row check accordingly. Otherwise, the first value will be skipped as though it were a heading. If your list has a heading in another row, set the input range to start immediately below that heading.

The last-row method uses Excel’s equivalent of moving up from the bottom of column A. It finds the last non-empty cell in that column, but it does not establish that every row above it is populated. Blank rows inside the data are harmless: the macro skips them and continues checking later rows.

What counts as a duplicate

Duplicate rules matter because “same value” can mean different things in different workflows. This macro uses a case-insensitive comparison for text. Thus Maple, maple, and MAPLE collapse to the first spelling encountered. If capitalization itself is meaningful in your data, the dictionary’s comparison setting and key construction need to be changed to use a case-sensitive comparison.

Leading or trailing spaces are not removed. For example, Maple and Maple are different strings, so both can appear in the results. If spaces are accidental, clean the source values first or modify the macro to trim text before building the key. Be cautious: trimming can change legitimate data, such as identifiers where spaces are significant.

The type prefix in the key prevents a text entry such as 12 from being merged with the numeric value 12. This is useful when a column mixes numbers and text identifiers, but it may not match your intended definition of duplicate. Dates stored as Excel serial numbers are read through Value2 as numbers; if you need to compare dates by their displayed text or a particular date format, define that rule explicitly before changing the key.

Order, speed, and large lists

The macro retains the first occurrence because it scans from the top down and adds a result only when the dictionary has not seen that key. It does not sort the output. For instance, if the source list is Birch, Cedar, Birch, Ash, the result is Birch, Cedar, Ash, not an alphabetical list.

A dictionary is useful for larger lists because it can check whether a key exists without comparing each new cell against every value already collected. That avoids the repeated scanning used by a simple nested-loop macro, which can become slow as the list grows. Reading the input into an array and writing the results as a block also reduces the number of interactions with the worksheet.

For a short list, the difference in speed may not matter. For a very large range, avoid adding extra cell-by-cell formatting or worksheet writes inside the main loop unless they are necessary. Also remember that the final result is limited by Excel’s worksheet row capacity; a unique list cannot occupy more rows than the worksheet allows.

Alternatives: UNIQUE and Advanced Filter

If you do not need a macro, Excel’s UNIQUE function is often the quickest option in Microsoft 365 or Excel 2021 and later. With source values in A2:A100, enter =UNIQUE(A2:A100) in an empty cell; Excel spills the unique results into the cells below. The formula recalculates when the source changes, unlike a macro’s fixed output, which must be refreshed by running the macro again.

For a one-time extraction without VBA, select the source range and use Data > Advanced. Choose Copy to another location, set the copy destination, and select Unique records only before applying the filter. Advanced Filter is useful in versions without the dynamic-array function, though it is a manual operation rather than a live formula.

Choose based on the task: use VBA when you need a repeatable button-driven process or want to incorporate uniqueness into a larger automation; use UNIQUE when a live, formula-based list is preferable; use Advanced Filter for a quick one-off result. None of these approaches automatically cleans inconsistent spaces or corrects misspellings.

Common problems and fixes

“Subscript out of range” when creating the output sheet: check for an existing sheet name that conflicts with Unique Results, including a chart sheet with that name, and choose a different results name if needed.
“ActiveX component can’t create object”: the dictionary is a Windows scripting component. On a Mac, use UNIQUE or Advanced Filter, or replace the dictionary approach with a VBA comparison loop.
The first value is missing: the code assumes row 1 is a header. Change the input range to start at row 1 if the source has no heading.
Results contain near-duplicates: check for spaces, nonprinting characters, or values stored inconsistently as numbers and text. The macro deliberately does not normalize those differences.
Nothing appears below the output heading: confirm that column A on the active sheet contains values below A1 and that the source sheet, rather than the results sheet, was active when the macro ran.

Frequently Asked Questions

How do I find unique values in column B with VBA?
Change the column references in the last-row calculation and input range from A to B. Keep the output reference to column A on Unique Results if you want the results to remain there.

Does this macro keep the first occurrence or the last one?
It keeps the first occurrence. The input is scanned from top to bottom, and later entries with an existing key are skipped.

Can I make the macro case-sensitive?
Yes. The current dictionary uses vbTextCompare, which ignores text case. A case-sensitive version needs a binary comparison setting and should be checked carefully if keys include mixed text and numeric types.

Will the results update when I edit the source cells?
No. VBA writes a snapshot of the unique values when the macro runs. Run the macro again after changing the source, or use the UNIQUE worksheet function for a dynamic list in a supported Excel version.

Can the macro include blank cells as a unique value?
Not as written: it skips empty cells and zero-length strings. If blanks should appear in the output, remove or revise the blank-checking conditions and decide whether a truly empty cell and a formula returning an empty string should count as the same value.

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.