How to Get a Unique List Using the UNIQUE Function in Excel VBA

In Excel 365 and later, you can easily create a list without duplicates (a unique list) using the UNIQUE function.

This feature is available not only on the worksheet but also from VBA. You can get the results directly as an array and use them in loop processing.

This article explains how to call the UNIQUE function from VBA to extract unique values.

Common Use Cases

• Creating a list of unique customers or products from a raw list containing duplicates.
• Preparing unique data before processing arrays in VBA.
• Passing a column of data with duplicates to get only unique values.

VBA Code Example

The following code extracts unique values from the cell range B3:B8 and outputs them to the debug window.

Sub GetUniqueList()

    Dim uniqueItems As Variant

    ' Extract values using the UNIQUE function (Excel 365+ only)
    uniqueItems = WorksheetFunction.Unique(Range("B3:B8").Value)

    ' Check the result (Output to the Immediate Window)
    Debug.Print "Unique Item List:"
    Dim i As Long
    For i = 1 To UBound(uniqueItems, 1)
        Debug.Print uniqueItems(i, 1)
    Next i

End Sub

Key Points of the Code

Important Notes

1. Version Compatibility: The UNIQUE function is available only in Excel 365 and later. It does not work in older versions (like Excel 2016).
2. Data Structure: The target range must be a 1-column or 1-row array. Be careful when handling multi-dimensional data.
3. Empty Data: If the cell range contains no values, Unique(...) will cause an error.

Summary

By using the UNIQUE function in VBA, you can easily get a list without duplicates and use it directly as an array.

A major advantage is that you do not need to use complex logic like filters or Dictionary objects. This keeps the process simple and fast.

If you are an Excel 365 user, this method allows for powerful data processing while keeping your code clean. Please try this approach for tasks such as aggregation, classification, and report creation.