Table of Contents
VBA (Visual Basic for Applications) is a programming language that can be used to automate tasks in Microsoft Excel. One useful task that can be accomplished using VBA is retrieving unique values from a column in an Excel spreadsheet. This involves creating a code that can search through a specific column and extract only the unique values, eliminating any duplicates. This can be helpful in data analysis and management, as it allows for easier identification and manipulation of distinct data points. By utilizing VBA, users can efficiently retrieve unique values from a column in Excel and streamline their data processing tasks.
VBA: 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:=TrueEnd 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:=TrueEnd 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 .
Additional Resources
The following tutorials explain how to perform other common tasks in VBA: