Excel VBA Unique Values: Code, Methods, and Practical Tips

VBA & macros · Updated November 10, 2026

To return unique values with Excel VBA, read the source cells, keep each value only the first time it appears, and write the results to a destination range. A VBA Scripting.Dictionary is a flexible way to do that; Excel’s Advanced Filter is a simpler built-in alternative when its range-based behavior suits your data.

What “unique” means in Excel VBA

“Unique” usually means a list with duplicate entries removed, but the exact result depends on what counts as a duplicate. For a list of names, you might treat “Mia” and “mia” as the same person, ignore blank cells, and keep the first spelling encountered. For product codes, by contrast, capitalization or leading spaces might matter. Decide on those rules before choosing a method.

VBA does not have a built-in worksheet function named UNIQUE that you can call as a standard VBA function in every Excel version. You can use VBA collections or dictionaries, call the Advanced Filter feature, or place a dynamic-array formula on a worksheet. The right option depends on whether you need a reusable macro, a one-time result, or a live list that updates when the source changes.

Use a Dictionary to collect unique values

A dictionary stores keys and associated items. In this example, each cleaned text value is a key, and its first original cell value is the item. The code scans A2:A100 on the active sheet, ignores blank cells and error values, compares text without regard to capitalization, and writes the result starting in C2. It keeps the order in which values first appear.

Sub WriteUniqueValues()
    Dim ws As Worksheet
    Dim source As Range
    Dim cell As Range
    Dim dict As Object
    Dim keys As Variant
    Dim results() As Variant
    Dim key As String
    Dim i As Long

    Set ws = ActiveSheet
    Set source = ws.Range("A2:A100")
    Set dict = CreateObject("Scripting.Dictionary")
    dict.CompareMode = vbTextCompare

    ' Add each nonblank, non-error value once.
    For Each cell In source.Cells
        If Not IsError(cell.Value2) Then
            key = Trim$(CStr(cell.Value2))
            If Len(key) > 0 Then
                If Not dict.Exists(key) Then
                    dict.Add key, cell.Value2
                End If
            End If
        End If
    Next cell

    ' Write the saved values as a single-column array.
    If dict.Count > 0 Then
        keys = dict.Keys
        ReDim results(1 To dict.Count, 1 To 1)
        For i = 0 To dict.Count - 1
            results(i + 1, 1) = dict(keys(i))
        Next i
        ws.Range("C2").Resize(dict.Count, 1).Value = results
    End If
End Sub

For example, if A2:A8 contains Oslo, Rome, Oslo, a blank, ROME, Tokyo, and Rome , the macro writes Oslo, Rome, and Tokyo in C2:C4. The comparison mode and trimming mean capitalization and surrounding spaces do not create extra entries. The first spelling encountered is retained.

This code uses late binding: CreateObject("Scripting.Dictionary") creates the dictionary without requiring you to add a reference in the VBA editor. That is convenient for sharing a workbook between Windows computers. It is important to set CompareMode before adding keys; changing it after items have been added can cause an error.

Run the macro safely

On Excel for Microsoft 365 on Windows, open the Visual Basic Editor with Alt+F11, choose Insert > Module, and paste the code into the module. Return to Excel, select the relevant sheet, and run WriteUniqueValues from Developer > Macros. If the Developer tab is hidden, enable it through File > Options > Customize Ribbon. On Mac, the Visual Basic Editor shortcut can depend on the keyboard and function-key settings; use Tools > Macro > Visual Basic Editor if the shortcut is unavailable.

  • Change A2:A100 to the actual source range, and change C2 if another output location is needed.
  • Make sure the destination does not overlap the source and contains no information you need to keep.
  • If the macro writes fewer values than a previous run, clear the old results below the new list yourself; this example does not erase leftover cells.
  • Save a macro-enabled workbook as .xlsm if you want to retain the VBA code.

Save a copy of the workbook before running a macro. VBA changes cannot be reversed with Ctrl+Z, so a backup is the practical way to recover from an unwanted output or an incorrect range. Only enable macros in files from sources you trust.

Use Advanced Filter for a quick copy

Advanced Filter can copy distinct entries to another location without building a dictionary. It is useful for a one-time operation or a short macro. It expects a header row, so include the heading in the source range and specify a destination heading cell. This example assumes a worksheet named Data with a header in A1 and records in A2:A100:

Sub CopyUniqueWithAdvancedFilter()
    Dim ws As Worksheet

    Set ws = ThisWorkbook.Worksheets("Data")

    ' Copy distinct records, including the header, to column C.
    ws.Range("A1:A100").AdvancedFilter _
        Action:=xlFilterCopy, _
        CopyToRange:=ws.Range("C1"), _
        Unique:=True
End Sub

Before running it, check the destination area: Advanced Filter writes the copied list there, so existing values could be overwritten. For data in multiple columns, Advanced Filter’s unique-record behavior considers the selected records as rows. Two rows that share a name but differ in another selected field are not identical records. A dictionary macro that examines only the name column answers a different question: which names occur at least once?

Choose between VBA, Advanced Filter, and UNIQUE

Pick a method based on how the result should behave, not just on how short the code is.

  • Dictionary VBA: Best when a macro needs explicit rules, such as ignoring case, trimming spaces, excluding errors, or preserving first-seen order. It can be adapted to loop through a changing range or process several columns.
  • Advanced Filter: Best for a straightforward copied list or distinct rows. It is a built-in Excel feature and does not require a dictionary reference, but its header and destination requirements make it less convenient for some layouts.
  • Worksheet UNIQUE: Best when you want a formula result that updates as source data changes. It is available in Microsoft 365 and Excel 2021 or later, but not in older perpetual versions such as Excel 2019.

With values in A2:A100, enter =UNIQUE(A2:A100) in a clear cell such as C2 to return a spilling list. This formula’s result recalculates as the input changes, while a macro’s output is a snapshot until you run the macro again. If you want the formula to sort its output as well, you can combine supported dynamic-array functions, but sorting is not part of the basic formula.

Excel for the web can calculate supported worksheet formulas, including UNIQUE where available, but it cannot create, edit, or run VBA macros. For browser-based use, a formula or an Excel feature available in the web app is a better fit. Mac desktop Excel supports VBA, although some automation features and external components can differ from Windows; test a workbook on the target platform when it will be shared.

Control blanks, spaces, case, and data types

Duplicate removal is only as consistent as the values being compared. The sample macro deliberately normalizes text keys by trimming leading and trailing spaces and comparing without case sensitivity. It skips formula errors rather than stopping on them, and it excludes cells that are empty or contain only spaces. These are choices, not universal rules.

Consider a source containing 0017 stored as text and 17 stored as a number. Converting both values to strings can make them equivalent or distinct depending on the exact formatting and normalization you choose. Similarly, dates and numbers can be represented differently from what a cell displays. If mixed types are meaningful in your data, define a key that records both the value’s type and its content, rather than relying on a simple text conversion.

To preserve spaces or treat uppercase and lowercase as different, remove the trimming step or set the dictionary’s comparison mode to binary comparison before adding entries. To keep one blank entry rather than skip blanks, change the blank-handling logic intentionally. Test such changes with a small sample that includes the edge cases your workbook actually contains.

Common problems and how to fix them

  • “Subscript out of range” on the sheet name: The Advanced Filter example refers to a tab named Data. Replace that text with the exact worksheet name in the workbook.
  • The results are in the wrong place: The dictionary macro uses the active sheet and writes at C2. Select the intended sheet before running it, or set ws to a named worksheet explicitly.
  • Old values remain below the list: The dictionary macro writes the current results but does not clear the rest of column C. Clear only the relevant old output range after checking that it contains no other data.
  • Values that look identical remain separate: Check for nonbreaking spaces, hidden characters, different number formats, or values stored as text versus numbers. Trimming ordinary spaces alone does not remove every invisible character.
  • The list does not update after edits: VBA output is static until the macro runs again. Use the UNIQUE worksheet formula if you need a result that recalculates automatically.

Frequently Asked Questions

Does Excel VBA have a UNIQUE function?
VBA does not provide the worksheet function UNIQUE as a universal built-in VBA function. Use a dictionary, Advanced Filter, or write a UNIQUE formula to a worksheet in a version of Excel that supports dynamic arrays.

How do I remove duplicates with VBA but keep the first value?
Scan the source from top to bottom and add a value only when the dictionary does not already contain its key. The example macro keeps the first encountered value and writes results in that same first-seen order.

Can the macro return unique rows instead of unique cells?
Yes, but the comparison key must represent all columns that define a row as a duplicate. Advanced Filter can also copy unique records when you select the full table; rows differing in any selected field remain distinct.

Why does UNIQUE work in a cell but not in my VBA code?
=UNIQUE(A2:A100) is worksheet formula syntax, not an ordinary VBA statement. Put it in a worksheet cell, or use VBA logic such as a dictionary to build a list directly.

Can I run VBA unique-value code in Excel for the web?
No. Excel for the web does not run VBA macros. Use a supported worksheet formula such as UNIQUE, or open the workbook in desktop Excel to run the macro.

Related

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.