In Microsoft Excel, while working with a dataset, sometimes we need to get the unique values to remove duplicates. Unique values are the values that exist just once in a dataset. We frequently have to get unique values from a set of data. Although Excel already has a built-in Remove Duplicates tool for this. But VBA is the most efficient, time-saving, and secure way to conduct any task in Excel. In this article, we will demonstrate different examples of Excel VBA to get unique values from a column.
Download Practice Workbook
You can download the workbook and practice with them.
4 Examples of Excel VBA to Get Unique Values from Column
Excel has built-in tools and functions to get the unique values from data. But with Excel VBA we can quickly get those unique values just by running the VBA code. To get unique values from a column we are going to use the following dataset. The dataset contains some products in column B and the country where the products will be delivered in column C. And we want to get the unique countries from column C to column E. So, let’s get into the examples to extract the unique countries from the column.
1. Find Exclusive Values from Column with Excel VBA
Suppose that, we will extract the unique values from column C and put those values in column E using Excel VBA Macros.
For this, we need to follow some procedures shown in the below section of this article. So, let’s have a look at the procedures below.
STEPS:
- Firstly, we need to go to the Developer tab from the ribbon.
- Secondly, click on Visual Basic to open the Visual Basic Editor from the Code category, where we will write down our codes. Or press Alt + F11 to open the Visual Basic Editor.
- Instead of doing this, you can just right-click on your worksheet and go to View Code. This will also take you to Visual Basic Editor.
- Thirdly, click on Module from the Insert drop-down menu bar. And, this will create a Module in your workbook.
- And, copy and paste the VBA code shown below.
VBA Code:
Sub Get_Unique_Values1()
Dim row As Long
row = Cells(Rows.Count, "C").End(xlUp).row
ActiveSheet.Range("C5:C" & row).AdvancedFilter _
Action:=xlFilterCopy, CopyToRange:=ActiveSheet.Range("E5"), _
Unique:=True
End Sub
- After that, run the code by clicking on the RubSub button or pressing the keyboard shortcut F5.
- And, by just using this code, you will be able to get the unique countries from the delivery country column.
VBA Code Explanation
Sub Get_Unique_Values1()
Sub is a portion of code that does not return any value but is utilized to handle the work in the code. Subprocedure is another name for it. So we name our procedure Get_Unique_Values1().
Dim row As Long
The DIM statement in VBA refers to “declare” and it must be used to declare a variable. So, we declare a variable.
row = Cells(Rows.Count, "C").End(xlUp).row
We are using this line of code to count the number of rows until the last row. So count every row which contains values in column C.
ActiveSheet.Range("C5:C" & row).AdvancedFilter Action:=xlFilterCopy, CopyToRange:=ActiveSheet.Range("E5"), Unique:=True
This line of code is for getting the unique values from column C and putting the result in column E.
End Sub
This line is for closing the procedure.
Read More: How to Get Unique Values from Range in Excel (8 Methods)
2. Excel VBA to Get Unique Values
Let’s see another example to get the unique values from the column. By the same token as before, we are using the same dataset for this example.
STEPS:
- To begin, go to the Developer tab on the ribbon.
- Then, from the Code category, click on Visual Basic to open the Visual Basic Editor.
- Or, to open the Visual Basic Editor is simply to press Alt + F11.
- Another way to open the Visual Basic Editor is just to right-click on the sheet, then select View Code.
- Next, go to Insert and select Module from the drop-down menu.
- And, this will open up the visual basic window.
- After that, write down the VBA code there.
VBA Code:
Sub Get_Unique_Values2()
Set myRng = Range("C5:C14")
Set r = Range("E5")
myRng.AdvancedFilter Action:=xlFilterCopy, _
CriteriaRange:=myRng, CopyToRange:=r, Unique:=True
End Sub
- Further, press the F5 key or click on the Run Sub button to run the code.
- And, you will get the unique values as shown in the first example.
VBA Code Explanation
Set myRng = Range("C5:C14")
Set r = Range("E5")
The VBA Set statement simply allows us to avoid having to type in the range we need to pick over and over again when running the code. So, we set our range to get the values from the range of cell C5:C14 and to put the unique values in column E.
myRng.AdvancedFilter Action:=xlFilterCopy, CriteriaRange:=myRng, CopyToRange:=r, Unique:=True
This line of code is for getting the unique values.
Read More: How to Extract Unique Values Based on Criteria in Excel
3. Extract Unique Values from Columns Using VBA in Excel
Let’s look at another example of utilizing Excel VBA to get unique values from a column. Similarly, as before, we are using the same dataset to get those values.
STEPS:
- First, click on the Developer tab.
- Second, launch the Visual Basic Editor by clicking on Visual Basic.
- Alternatively, you may access the Visual Basic Editor by pressing Alt + F11.
- Or, right-click on the sheet and choose View Code from the menu.
- Next, pick the Module from the drop-down box under Insert.
- And the visual basic window will appear.
- Write the code there.
VBA Code:
Sub Get_Unique_Values3()
Dim myArr As Variant
Dim rowC As Long
With Sheet9
Sheets("Example3").Columns("C:C").AdvancedFilter _
Action:=xlFilterCopy, CopyToRange:=.Range("E2"), Unique:=True
rowC = .Cells(.Rows.Count, "C").End(xlUp).row
myArr = .Range("C3:C" & row)
End With
Dim myVal As String
Dim a As Integer
For a = 1 To UBound(myArr)
myVal = myVal & myArr(a, 1) & ","
Next
End Sub
- Press the F5 key to run the code or click on the Run Sub button.
- Finally, you will get your desired unique values from the column shown in the first example.
VBA Code Explanation
Dim myArr As Variant
Dim rowC As Long
Declaring the variables.
With Sheet9
The With statement enables us to make a sequence of statements on a single object without having to requalify the object’s name. As we work on Sheet9, we take the sheet number.
Sheets("Example3").Columns("C:C").AdvancedFilter Action:=xlFilterCopy, CopyToRange:=.Range("E2"), Unique:=True
This line of code is for getting the unique values from the column. First, we take the sheet name and then the column of this sheet from which we want to extract the unique values.
rowC = .Cells(.Rows.Count, "C").End(xlUp).row
myArr = .Range("C3:C" & row)
This line of code is for counting the total number of rows in that particular column, which has the value and putting those in an array.
Dim myVal As String
Dim a As Integer
Again we are declaring some variables for performing a loop.
For a = 1 To UBound(myArr)
myVal = myVal & myArr(a, 1) & ","
Next
Those lines of code will find the unique values one-by-one by performing a loop until the array will not end.
Read More: VBA to Get Unique Values from Column into Array in Excel (3 Criteria)
4. Run VBA Macro to Extract Unique Values from Column into MsgBox
Let’s explore another Excel VBA Macro example to get unique values from a column. But this time we will show the result in a Msgbox. Like the previous example, we are using the same dataset.
STEPS:
- In the beginning, go to the Developer tab > Visual Basic > Insert > Module.
- Or, right-clicking on the worksheet will open up a window. From there go to the View Code.
- And, this will take you to the Visual Basic Editor field, where we can write VBA Macros.
- On the other hand, pressing Alt + F11 will also open the Visual Basic Editor.
- After that, type the VBA code.
VBA Code:
Sub Get_Unique_Values4()
mySheet = Sheets("Example4").Range("C5:C14")
With CreateObject("scripting.dictionary")
For Each myData In mySheet
a = .Item(myData)
Next
MsgBox Join(.keys, vbLf)
End With
End Sub
- Finally, run the code by pressing F5 on your keyboard.
- And you see the result in Msgbox.
Read More: How to Extract Unique Items from a List in Excel (10 Methods)
Conclusion
The above methods will assist you to get unique values from a column in Excel VBA. Hope this will help you! If you have any questions, suggestions, or feedback please let us know in the comment section. Or you can have a glance at our other articles in the ExcelDemy.com blog!
In your first example, Canada comes up twice. Same thing happens to me and I don’t know why.
Hello, CY!
Yeah, actually this is because I set the cell first as “Range(“C5:C” & row)”. Here as I set cell C5, the element of the C5 cell will show up as the first unique value. You can use other VBA codes also if you want to get unique values with excel features, check this article- https://www.exceldemy.com/excel-unique-values-in-column/
Hope this will help you!
Hello Sabrina.
Thank you for explainning and sharing the code.
In a couple of the modules, I get
run-time error ‘1004’:
Method ‘Range’ of object’_Worksheet’ failed
Any clue?
Hello MP ROY,
Thank you for your comment. I have tried these codes and they are working perfectly, except the code in Example3. For that particular code, you can use a new workbook. While I was using a new workbook the code has been perfectly worked there. But you have to be careful about the name of worksheet. You have to use exact worksheet number and name in the code.
Regards,
Musiha
Team ExcelDemy
Hi dear,
Im Nirmal with you
I have some data with 13900 rows and 6 columns. column 1 showing Item name, among the items name some items repeated 2,3 or 4 times. I need to split this excel file which show row data of only unique item names and another sheets show data having duplicate barcode. is anyone can help me.
Dear Nirmal,
Could you please provide me with your Excel file that you have mentioned or some screen shots from your dataset? That would help me to understand your dataset and answer your query accordingly.
Thanks for your query.
Regards,
Adnan
Team ExcelDemy