[Excel VBA] How to Extract Unique Data (Unique List) Using the UNIQUE Function

Creating a unique list by removing duplicate values from a data set is fundamental for data aggregation and analysis.

Traditionally, it was common to use Collection or Dictionary objects in VBA to achieve this. However, in the latest versions of Excel, a powerful function has been introduced that can handle this process in a single line.

That is the UNIQUE function. In Excel for Microsoft 365 and Excel 2021 or later, calling this UNIQUE function directly from VBA allows you to write deduplication logic that is amazingly simple.

[Important] Environment Where UNIQUE Function is Available

To use this technique, your Excel must be a version that supports the UNIQUE function (Microsoft 365 or Excel 2021 and later). Please note that this code cannot be executed in older versions of Excel.

The Completed VBA Code

Below is the VBA code that extracts a unique list of products from column B of the “SalesData” sheet and writes it to column D of the “Summary” sheet.

Sub GetUniqueListWithUniqueFunction()

    ' Declare variables
    Dim sourceRange As Range
    Dim uniqueList As Variant
    Dim outputCell As Range

    '--- Settings ---
    ' The range containing the source data to deduplicate
    Set sourceRange = ThisWorkbook.Worksheets("SalesData").Range("B2:B500")
    ' The starting cell where results will be output
    Set outputCell = ThisWorkbook.Worksheets("Summary").Range("D2")
    '--- End Settings ---
    
    ' --- 1. Get a unique list as an array using the UNIQUE function ---
    On Error Resume Next ' Avoid errors if the range is empty
    uniqueList = WorksheetFunction.Unique(sourceRange)
    On Error GoTo 0
    
    ' If the result is empty (error occurred), exit
    If IsEmpty(uniqueList) Then
        MsgBox "Could not retrieve a unique list from the target range.", vbExclamation
        Exit Sub
    End If
    
    ' --- 2. Write the resulting array to the specified cell at once ---
    ' Clear existing data in the destination
    outputCell.Resize(Rows.Count - outputCell.Row + 1, 1).ClearContents
    
    ' Use Resize to expand the destination range to match the array size and assign values
    outputCell.Resize(UBound(uniqueList, 1), UBound(uniqueList, 2)).Value = uniqueList

    MsgBox "Extraction of the unique list is complete."
    
End Sub

Explanation of Key Points

1. WorksheetFunction.Unique

uniqueList = WorksheetFunction.Unique(sourceRange)

This is the core of the process. We are calling the UNIQUE function from VBA. The UNIQUE function removes duplicate values from the provided cell range (sourceRange) and returns the result as a 2-dimensional array. To receive this return value, the variable uniqueList is declared as a Variant type.

Main Arguments of the UNIQUE Function UNIQUE(array, [by_col], [exactly_once])

• array (Required): The target cell range.
• [by_col] (Optional):
  • False (Default): Compares rows and returns unique rows.
  • True: Compares columns and returns unique columns.
• [exactly_once] (Optional):
  • False (Default): Returns items that appear at least once (standard deduplication).
  • True: Returns only items that appear exactly once (completely unique).

Example: If the list contains “Apple, Orange, Apple”:

• If exactly_once:=False, the result is “Apple, Orange”.
• If exactly_once:=True, the result is only “Orange”.

2. Writing the Resulting Array to the Sheet

outputCell.Resize(UBound(uniqueList, 1), UBound(uniqueList, 2)).Value = uniqueList

This is a standard technique for writing the array returned by the UNIQUE function to the worksheet in one go.

1. UBound(uniqueList, 1) gets the number of rows in the array.
2. UBound(uniqueList, 2) gets the number of columns in the array.
3. The .Resize method expands the destination start cell (outputCell) to the exact size of the array.
4. By assigning the array directly to the .Value property of the expanded range, all data is written instantly.

Summary

In modern Excel environments where the UNIQUE function is available, creating unique lists has become dramatically easier.

1. Use WorksheetFunction.Unique(TargetRange) to get a unique list as an array.
2. Write the array to the sheet at once using .Resize and .Value.

You no longer need to build complex logic using Collection or Dictionary objects; you can complete the process at high speed with just a few lines of code. If you are using Excel 365 or 2021, please utilize this smart method.