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:A100to the actual source range, and changeC2if 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
.xlsmif 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
wsto 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
- Markdown Merge Cells: What Works and What Does Not
- AI Data Validation: How It Works and Why It Matters
- LibreOffice: How to Merge Table Cells in Writer and Calc
Try it: the Column Letter to Number: aA is 27, XFD is 16,384. Converts both ways.
More in VBA & macros
- Access VBA Find Text: Methods, Examples, and FixesTo find text in Access VBA, use a DAO recordset’s FindFirst method with a criterion such as [LastName]…
- Autofit VBA Range: Columns, Rows, and ExamplesIn Excel VBA, use the AutoFit method to size worksheet columns or rows to fit their contents. For…
- Excel Macro to Hide Columns: VBA Examples and TipsTo hide columns with a VBA macro in Excel for Microsoft 365 on Windows, put Columns("B:D").Hidden = True…
- VBA AutoFilter: What It Filters and How to Use ItVBA AutoFilter filters the rows in an Excel list or table according to criteria in one or more…
- Keyboard Recorder Macro: How It Works and When to Use OneA keyboard recorder macro is a tool that captures a sequence of keystrokes—and sometimes mouse actions or delays—so…
- VBA Dictionary Example: Create, Find, and Count ItemsA VBA dictionary stores unique keys with associated values, making it useful for counting repeated entries or looking…
Sources: Function behaviour and menu paths: Microsoft Support documentation for Excel. Method & sources
Spotted an error in this guide? Let us know.