VBA Remove Duplicates

Duplicate values are often not required in Excel, especially when you want to have unique values count. It is because we usually have a different set of data to work with, and we see a bunch of duplicate values in it.

We hope you are familiar with removing duplicates in excel worksheets. If not, there is nothing to worry about. We will show you a simple example for you. In VBA, too, we can perform the remove duplicates method.

So, It has removed all the duplicate values of the "Region" heading. Similarly, we can do this task with the help of the VBA code.

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.

: Which selection column do we need to remove duplicates? We need to mention the column number of the selected range.

: The range you have selected has either headers or not. So, 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, you can select this.
• xlGuess: This option will allow Excel to guess the data headers.

So, using these parameters, we can remove duplicates with just a click of a button without breaking our sweat.

In the below section, we will show examples to VBA removing duplicates. Follow the steps carefully to write the code on your own.

Below are the examples of RemoveDuplicate in Values VBA.

VBA Remove duplicates - Example #1

Consider the below data for this example as well.

We need to remove "Region" column duplicates from the above data, so follow the steps below to write the code.

Step 1: Start the sub procedure by giving a Macro code name.

Step 2: Mention the range of data by using the VBA Range object.

Code:

Sub Remove_Duplicates_Example1()

  Range ("A1:C9").

End Sub

Step 3: After mentioning the range access VBA "RemoveDuplicates" method.

Code:

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, we need to remove the duplicates from the first column.

Code:

Sub Remove_Duplicates_Example1()

  Range("A1:C9").RemoveDuplicates Columns:=1,

End Sub

Step 5: Next thing is whether the data has headers or not. In this case, we have headers, so select "xlYes."

Code:

Sub Remove_Duplicates_Example1()

  Range("A1:C9").RemoveDuplicates Columns:=1, Header:=xlYes

End Sub

Run this code. It will remove duplicates from the selected region.

It is a straightforward way of referring to the range of cells. However, if you wish to select the range on your own and remove duplicates, then we need to use the variable to work with. In the below example, we will show you how to use variables in VBA.

VBA Remove duplicates - Example #2

In the above example, we have specifically supplied the range of cells. Now, we will see how to work with selecting our cells.

For example, we have a few data sets, as shown in the image below.

We cannot explicitly specify the range of cells so we will assign the selection as the range.

Step 1: Declare the variable as Range.

Code:

Sub Remove_Duplicates_Example2()

  Dim Rng As Range

End Sub

Step 2: Range is an object. We will set the range as our selection.

Code:

Sub Remove_Duplicates_Example2()

  Dim Rng As Range
  
  Set Rng = Selection

End Sub

Step 3: Instead of a range of cells, we can use the variable "rng."

Code:

Sub Remove_Duplicates_Example2()

  Dim Rng As Range

  Set Rng = Selection

  Rng.RemoveDuplicates Columns:=1, Header:=xlYes

End Sub

Before running the code, we must 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 to remove duplicate values from excel columns. Finally, we need to use an array to remove multiple columns and mention the column numbers.

For example, look at the example data image.

We have duplicate values in the first column and fourth column. So, we will remove these columns. Use the below code to VBA to remove duplicates.

Code:

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 article has been a guide to VBA Remove Duplicates. Here, we learn how to remove duplicate values in Excel VBA, examples and download an Excel template. Below are some useful Excel articles related to VBA: -

• VBA Like
• VBA Pivot Table
• Find Duplicates in Excel
• Highlight Duplicates in Excel