To create a unique list with Excel VBA, read the source range, keep the first occurrence of each value, and write the results to a separate range. On Windows, a Scripting.Dictionary makes this straightforward; the macro below skips blank cells, compares text without regard to case, and preserves the original value from the first match.
What a unique list means in Excel
A unique list contains one entry for each value or item you consider distinct. For example, if A2:A7 contains North, South, North, East, South, and West, the unique list in first-seen order is North, South, East, West. VBA can build that list once, or it can be adapted to run whenever you choose.
“Unique” does not automatically specify how to treat capitalization, spaces, blanks, errors, or values that look alike but have different underlying types. The right rules depend on the data. In the example macro, “North” and “north” count as the same item, leading and trailing spaces are ignored when comparing text, blank and whitespace-only cells are skipped, and the first original value is retained for output. Excel errors are skipped too.
Those choices are deliberate, not universal. If capitalization matters, or if a numeric value and a text value containing the same digits must remain separate, change the comparison logic before using the macro on important data.
Choose the right way to make the list
VBA is useful when a repeatable macro is part of a larger task, such as preparing a report or cleaning an imported worksheet. It is not always the simplest option. Consider these approaches:
- VBA: Good for a repeatable workflow or for combining list creation with other macro steps. The result is a fixed list until the macro runs again.
- UNIQUE: A formula option in Microsoft 365 and Excel 2021 or later. It spills results into nearby cells and updates when its source changes.
- Remove Duplicates: A quick one-time cleanup, but it changes the selected data rather than creating a separate automatically updating list.
- Advanced Filter: A built-in option for copying unique records to another location without writing VBA.
Use a separate output range for the macro rather than putting results over the source. Keeping the original data intact makes the process easier to check and repeat.
How the dictionary macro works
A dictionary stores keys and associated items. Here, each normalized cell value becomes a key. Before adding a value, the macro checks whether that key is already present. If it is not, it adds the key and saves the original cell value in an output array. Because the macro scans from top to bottom, the output keeps the first-seen order.
This version uses late binding: it creates the dictionary in code rather than requiring you to select a reference in the VBA editor. The vbTextCompare setting makes text comparisons case-insensitive. It must be set before keys are added.
In the example, the input is A2:A100 on the active worksheet, and the output is written from C2 down. Change those range references to match your workbook. The procedure clears the corresponding output area in column C first, so do not place unrelated information in C2:C100.
VBA code for a unique list on Windows
On Windows, press Alt+F11 to open the Visual Basic Editor, choose Insert > Module, and paste in this procedure. If the Developer tab is visible, Developer > Visual Basic also opens the editor.
Option Explicit
Sub CreateUniqueList()
Dim ws As Worksheet
Dim sourceRange As Range
Dim outputRange As Range
Dim cell As Range
Dim dict As Object
Dim results() As Variant
Dim value As Variant
Dim key As String
Set ws = ActiveSheet
Set sourceRange = ws.Range("A2:A100")
Set outputRange = ws.Range("C2").Resize(sourceRange.Cells.Count, 1)
Set dict = CreateObject("Scripting.Dictionary")
' Compare text without regard to capitalization.
dict.CompareMode = vbTextCompare
ReDim results(1 To sourceRange.Cells.Count, 1 To 1)
For Each cell In sourceRange.Cells
value = cell.Value2
' Ignore Excel errors and empty or whitespace-only cells.
If Not IsError(value) Then
key = Trim$(CStr(value))
If Len(key) > 0 Then
If Not dict.Exists(key) Then
dict.Add key, True
results(dict.Count, 1) = value
End If
End If
End If
Next cell
' Clear old results, then write the new list if one exists.
outputRange.ClearContents
If dict.Count > 0 Then
ws.Range("C2").Resize(dict.Count, 1).Value = results
End If
End Sub
The results array is sized to the maximum possible number of unique values, which is the number of source cells. The macro writes only the portion needed for the actual count. For example, if A2:A100 contains four distinct nonblank items under the comparison rules above, the macro writes four values to C2:C5 and clears the remaining cells in C6:C100.
Before running it, save a copy of the workbook. A macro’s changes cannot be undone with Ctrl+Z. To run the procedure, return to Excel, press Alt+F8, select CreateUniqueList, and choose Run. If prompted, save the workbook in a macro-enabled format such as .xlsm. Macro security settings may also need to allow the workbook’s macros.
Mac compatibility and a portable VBA option
The dictionary example uses Windows COM automation through CreateObject. That dependency means you should not assume it will run in Excel for Mac. If portability matters, a simple nested-loop comparison avoids the dictionary and checks each new item against values already collected. It is slower for large lists, but can suit modest ranges and does not rely on the Windows dictionary object.
This alternative uses the same A2:A100 source and C2 output layout. It compares text without regard to case, trims spaces for comparison, skips errors and blanks, and preserves the first original value.
Option Explicit
Sub CreateUniqueListPortable()
Dim ws As Worksheet
Dim sourceRange As Range
Dim outputRange As Range
Dim cell As Range
Dim results() As Variant
Dim value As Variant
Dim uniqueCount As Long
Dim j As Long
Dim isNew As Boolean
Set ws = ActiveSheet
Set sourceRange = ws.Range("A2:A100")
Set outputRange = ws.Range("C2").Resize(sourceRange.Cells.Count, 1)
ReDim results(1 To sourceRange.Cells.Count, 1 To 1)
For Each cell In sourceRange.Cells
value = cell.Value2
If Not IsError(value) Then
If Len(Trim$(CStr(value))) > 0 Then
isNew = True
For j = 1 To uniqueCount
If StrComp(Trim$(CStr(value)), _
Trim$(CStr(results(j, 1))), vbTextCompare) = 0 Then
isNew = False
Exit For
End If
Next j
If isNew Then
uniqueCount = uniqueCount + 1
results(uniqueCount, 1) = value
End If
End If
End If
Next cell
outputRange.ClearContents
If uniqueCount > 0 Then
ws.Range("C2").Resize(uniqueCount, 1).Value = results
End If
End Sub
Use one procedure or the other, not both in the same run: each writes to column C. Excel for Mac has a Visual Basic Editor, but keyboard shortcuts can depend on the Mac’s function-key settings; use the Excel menus if the shortcut does not open it. Excel for the web cannot run VBA macros. A workbook containing VBA must be opened in a desktop version of Excel to run the code.
Adjust the comparison rules
Small changes in the input can affect what counts as a duplicate. The dictionary version converts each cell value to text for its key, then trims surrounding spaces. Consequently, numeric 12 and text “12” can share a key, as can “Blue” and “ blue ”. The output still uses the first cell’s original value, so formatting and the underlying value of that first item are not replaced by the key.
If you need a case-sensitive list, use a binary comparison for text keys instead of vbTextCompare. If spaces are meaningful, remove Trim$ from the key-building step and from the portable version’s comparisons. If error values should appear in the output, they need explicit handling rather than being passed through CStr. Define these rules based on the meaning of the data, not just how the cells appear on screen.
For a list based on whole records rather than one column, build a composite key from the fields that define a duplicate. For example, a customer record might be considered duplicate only when both email address and region match. A separator between fields helps avoid ambiguous combined keys; for complex data, validate the key design so different field combinations cannot produce the same text.
Formula and built-in alternatives
If you want a live list rather than a macro-generated snapshot, formulas may be a better fit. With source values in A2:A100 and the formula entered in D2, =UNIQUE(A2:A100) returns one instance of each value and spills the results below the formula. UNIQUE is available in Microsoft 365 and Excel 2021 or later. The spill area must be empty, or Excel can return a #SPILL! error.
To return a sorted result instead of first-seen order, with the same A2:A100 source and D2 as the formula cell, use =SORT(UNIQUE(A2:A100)). UNIQUE and SORT are also available in Microsoft 365 and Excel 2021 or later. These formulas recalculate as the source range changes, unlike a VBA output list, which must be regenerated by running the macro.
For a one-time operation, select the data and use Data > Remove Duplicates. This removes duplicate rows from the selected range, so work on a copy if you need the original data. To copy unique records elsewhere, use Data > Advanced in the Sort & Filter group, select Copy to another location, choose the list range and copy-to range, then select Unique records only.
Common problems and checks
- “ActiveX component can’t create object” or a similar error: The dictionary object may be unavailable in that environment, or the code may be running outside Windows desktop Excel. Try the portable procedure or a worksheet-based alternative.
- The list is missing items: Check whether items differ only by capitalization or surrounding spaces. The sample treats those differences as duplicates by design.
- Old values remain after a shorter result: The example clears C2:C100 before writing new results. If you changed the source range, make sure the output-clearing range is still large enough for the previous list.
- Results overwrite other data: Move the output range or change the destination reference. Keep source and output separate, and inspect the destination before running the macro.
- The macro is blocked: Check that the file is macro-enabled and that Excel’s security settings permit the workbook’s macros. Do not enable macros in files from untrusted sources.
For a quick check, compare the number of results with the number of distinct items you expect under the chosen rules. Test first with a small sample that includes a repeated value, a blank, a differently capitalized value, and a value with extra spaces. That makes the macro’s behavior visible before you apply it to a larger worksheet.
Frequently Asked Questions
How do I create a unique list in Excel VBA?
Loop through the source range, check whether each value has appeared before, and write only new values to a separate range. The Windows example uses a dictionary to perform the check efficiently.
Does the macro keep the original order?
Yes. Both examples scan the source from top to bottom and add a value only the first time it appears, so the output follows first-seen order.
Can VBA make the list update automatically?
The examples update when you run them; they do not recalculate automatically like a dynamic-array formula. You can connect a macro to a button or another event, but event-driven code needs extra care to avoid repeated runs and unintended worksheet changes.
Can I make the list case-sensitive?
Yes. Change the dictionary comparison mode to a binary comparison, and use vbBinaryCompare in the portable version’s StrComp call. Then values such as “East” and “east” can be treated as different items.
Can I use this macro in Excel for the web?
No. Excel for the web can preserve a workbook containing VBA, but it does not run VBA macros. Open the workbook in desktop Excel to execute the procedure.
Related
- Excel Shortcut to Insert a Row Every Other Row in a List
- Excel Shortcut to Insert a Copied Row
- Excel Shortcut for Inserting Rows Above or Below
Try it: the Column Letter to Number: aA is 27, XFD is 16,384. Converts both ways.
More in VBA & macros
- VBA Find a Value in a Column: Methods and ExamplesTo find a value in an Excel column with VBA, use the Range.Find method on the column and…
- Excel VBA: Add a Keyboard Shortcut to a MacroYes. In Excel for Microsoft 365 on Windows, open Developer > Macros, select your macro, choose Options, and…
- Excel VBA AutoFilter Multiple Criteria: A Practical GuideTo filter a range with Excel VBA using multiple allowed values in one column, pass an array to…
- Excel VBA Freeze Panes at a Specific CellYes. Activate the worksheet, select the cell that marks the top-left corner of the scrolling area, then set…
- Create a Pivot Table in Excel Using VBATo create a PivotTable with VBA, prepare a rectangular data range with clear headers, then use a PivotCache…
- Excel VBA: Create an Outlook Email Draft in WindowsYes. In Excel for Microsoft 365 on Windows, VBA can use classic Outlook’s object model to create an…
Sources: Function behaviour and menu paths: Microsoft Support documentation for Excel. Method & sources
Spotted an error in this guide? Let us know.