VBA: How to Get Unique Values from Column

by Zach Bobbitt Published on June 30, 2023

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

Zach Bobbitt

Hey there. My name is Zach Bobbitt. I have a Masters of Science degree in Applied Statistics and I’ve worked on machine learning algorithms for professional businesses in both healthcare and retail. I’m passionate about statistics, machine learning, and data visualization and I created Statology to be a resource for both students and teachers alike. My goal with this site is to help you learn statistics through using simple terms, plenty of real-world examples, and helpful illustrations.

4 Replies to “VBA: How to Get Unique Values from Column”

NARENDAR PRASHAR says:

June 18, 2025 at 12:37 pm

Hi Zach,

Found this very useful, thank you. HOwever, I actually need to go one step further and am not sure if this is possible. To try to explain,I have a table of transactions from accounts where the different accounts are both buying and selling. Is it therefore possible from this table to get a unique list of the account names/codes where they carried out buy transactions (signified by a Buy in a buy/sell column) and also then get a separate that will also shows a unique list of accounts which have carried out Sell transactions. I can think of a workaround whereby I first create 2 separate buy and sell columns that only are each only populated with the account code where the transaction was a buy or a sell and then applying the code you have suggested above to each, but was thinking there may be a more elegant way to get the same result.

Thanks in advance,

Narendar.

Reply
1. James Carmichael says:
  
  June 18, 2025 at 4:32 pm
  
  Hi Narendar…Thank you for your feedback and suggestions!
  
  Reply
2. jake owsley says:
  
  November 20, 2025 at 10:00 pm
  
  It might be easier to break down into sells and buys first, then extract what you want from each, which is I think what you suggested.
  
  Can you give a sample spreadsheet of what you would like, before and after?
  
  Reply
3. jake owsley says:
  
  November 20, 2025 at 10:01 pm
  
  It might be easier to break down into sells and buys first, then extract what you want from each, which is I think what you suggested. Can you give a sample spreadsheet of what you would like, before and after?
  
  Reply