VBA: How to Get Unique Values from Column

You can use the AdvancedFilter function in VBA to quickly get a list of unique values from a column in Excel.

Here is one common way to do so:

Sub GetUniqueValues()

Range("A1:A11").AdvancedFilter _
                Action:=xlFilterCopy, CopyToRange:=Range("E1"), Unique:=True

End Sub

This particular example extracts a list of unique values from the range A1:A11 and displays them starting in cell E1.

The following example shows how to use this syntax in practice.

Example: Get Unique Values from Column Using VBA

Suppose we have the following dataset in Excel that contains information about various basketball players:

Suppose we would like to extract a list of unique values from the Team column.

We can create the following macro to do so:

Sub GetUniqueValues()

Range("A1:A11").AdvancedFilter _
                Action:=xlFilterCopy, CopyToRange:=Range("E1"), Unique:=True

End Sub

When we run this macro, we receive the following output:

Column E now displays a list of unique values from the Team column in the original dataset.

Note that the AdvancedFilter method is case-insensitive.

For example, if we had the team names “MAVS” and “Mavs” in the same column then this particular macro would only return the first of these two values to occur in the Team column since they share the exact same characters.

Note: You can find the complete documentation for the AdvancedFilter method in VBA here.

Additional Resources

The following tutorials explain how to perform other common tasks in VBA:

VBA: How to Count Cells with Specific Text
VBA: How to Count Number of Rows in Range
VBA: How to Count Unique Values in Range