VBA Remove Duplicates (Table of Contents)
RemoveDuplicates in VBA Excel
Duplicate values are often not required in excel especially when you want to have unique values count. We usually have a different set of data to work with and we see a bunch of duplicate values in it.
I hope you are familiar with removing duplicates in excel worksheet, if not nothing to worry we will show you a simple example for you. In VBA too we can perform remove duplicates method.
So, It has removed all the duplicate values of “Region” heading. Similarly, we can do this task with the help of VBA code.
How to Remove Duplicate Values in VBA Coding?
In order to remove duplicate values first, we need to mention the range we are referring to, then we can access the “Remove Duplicates” method. So syntax will be as follows.
[Column]: Which column of the selection we need to remove duplicates? We need to mention the column number of the selected range.
[Header]: The range that you have selected has headers or not. We have three options to work with here.
- xlYes: If the data has headers then you can select this.
- xlNo: If the data doesn’t have headers then you can select this.
- xlGuess: This option will allow the excel to guess the headers of the data.
So using these parameters we can remove duplicates with just a click of a button without breaking our sweat.
In the below section I will show you some of the examples to VBA remove duplicates. Follow the steps carefully to write the code on your own.
Examples of Remove Duplicate Values in VBA Coding
Below are the examples of Remove Duplicate in Values VBA.
VBA Removeduplicates – Example #1
Consider the below data for this example as well.
From the above data, we need to remove “Region” column duplicates so follow below steps to write the code.
Step 1: Start the sub procedure by giving a macro code a name.
Step 2: Mention the range of data by using the VBA Range object.
Sub Remove_Duplicates_Example1() Range ("A1:C9"). End Sub
Step 3: After mentioning the range access VBA “RemoveDuplicates” method.
Sub Remove_Duplicates_Example1() Range("A1:C9").RemoveDuplicates End Sub
Step 4: First argument in which column we need to remove the duplicate values. In this example from the first column, we need to remove the duplicates.
Sub Remove_Duplicates_Example1() Range("A1:C9").RemoveDuplicates Columns:=1, End Sub
Step 5: Next thing is whether data has headers or not. In this case, we have headers so select “xlYes”.
Sub Remove_Duplicates_Example1() Range("A1:C9").RemoveDuplicates Columns:=1, Header:=xlYes End Sub
Run this code it will VBA remove duplicates from the selected region.
This is an explicit way of referring to the range of cells. If you wish to select the range on our own and then remove duplicates then we need to use the variable to work with. In the below example I will show you how to use variables in VBA.
VBA Removeduplicates – Example #2
In the above example, we have specifically supplied the range of cells. Now we will see how to work with the selection of own cells.
For example, I have a few sets of data as shown in the below image.
Each time I cannot specify the range of cells explicitly, so we will assign the selection as the range.
Step 1: Declare the variable as Range.
Sub Remove_Duplicates_Example2() Dim Rng As Range End Sub
Step 2: Range is an object we will set the range as our selection.
Sub Remove_Duplicates_Example2() Dim Rng As Range Set Rng = Selection End Sub
Step 3: Now instead of a range of cells we can use the variable “rng”.
Sub Remove_Duplicates_Example2() Dim Rng As Range Set Rng = Selection Rng.RemoveDuplicates Columns:=1, Header:=xlYes End Sub
Before we run the code we need to select the range of cells first then we can remove duplicates from the selected range of cells.
VBA Remove Duplicates from Multiple Columns – Example #3
We can also use VBA remove duplicate values from multiple columns as well. In order to remove multiple columns, we need to use Array and mention the column numbers.
For example, look at the below example data image.
We have duplicated values in the first column and fourth column. So we will remove from these columns. Use below code to VBA remove duplicates.
Sub Remove_Duplicates_Example3() Dim Rng As Range Set Rng = Range("A1:D9") Rng.RemoveDuplicates Columns:=Array(1, 4), Header:=xlYes End Sub
You can download this VBA Remove Duplicates Excel here. VBA Remove Duplicates Excel Template
This has been a guide to VBA Remove Duplicates. Here we learn how to remove duplicates values in Excel VBA along with examples & download excel template. Below are some useful excel articles related to VBA –