Excel VBA remove duplicates method

In this post, you’ll learn different methods of Excel VBA remove duplicates.

If the data in one row find an exact match with any other rows in a given range then it’ll be considered as duplicate and using remove duplicates function you can remove the duplicates and keep unique value.

The syntax of Remove Duplicates in Excel VBA

Range.RemoveDuplicates(Column, Header)

Range- It defines the given range specified by the user

Column- It defines the array of indexes of columns for checking the duplicate values. For e.g, if you gave the column position as 5,6,7 then it checks the duplicates only in column 5,6 and 7 by taking the first column in the selected range as the reference.

Header- It determines whether the given range does contain a header or not. The user can specify three values in the header parameter. They are shown below

Header:=xlYes- Choose this value if a given range does contain Header

Header:=xlNo – Choose this value if given Range doesn’t contain header

Header:=xlGuess- Choose this value if you want to excel to determine the existence of Header.

VBA remove duplicates in a single column

How to remove duplicates in single column using VBA -vba remove duplicates

You can find the Month names listed in column B of “sheet 1” and it does contain header “Month”. Column B contains few month names which are repeated over more than one time. We are now going to use VBA to Remove duplicate values in Column B (Single column) of Range B1: B20. This VBA duplicate function code check only the occurrence of duplicate values in the given range and it doesn’t affect the values outside the specified range.

The above code contains array value as 1 so it finds the duplicates only in the column B and Header value is xlYes so it ignores the first row.

 VBA remove duplicates in a given Range

How-to-remove-duplicates-in-a-given-Range-vba remove duplicates

Above data contain Marks of three different subjects scored by 11 Students. We are now going to use VBA to remove duplicate marks. For e.g., if more than one student secured the same marks in Maths, physics, and chemistry then we are going to remove all the duplicates rows. All the colored rows contain duplicate marks and using VBA we are going to remove the multiple occurrences and keep a unique value.


The above code contains array value as 1,2,3 so it finds the duplicates by looking at the values in column A, B, and C of each row and Header value is xlYes so it ignores the first row.

 

VBA remove duplicates in a given range for specified columns

VBA to remove duplicates in a given range for specified columns

 

Above data contain Marks of four different subjects scored by 12 Students. We are now going to use VBA to remove duplicate marks of two subjects Maths and Physics. For e.g., if more than one student secured the same marks in Maths and Physics then we are going to remove the duplicates rows. Some rows contain duplicate marks and using VBA we are going to remove the multiple occurrences and keep a unique value.

The above code contains array value as 1 and 2 so it finds the duplicates by looking at the values in column A and B  of each row and Header value is xlYes so it ignores the first row.

 

VBA to remove duplicates in an unknown range for specified columns

We are going to use the same data but we assume that we don’t know the range. In this case, we have to use UsedRange property to determine the range before proceeding with remove duplicates.

To do that we need to replace Set Rng = ThisWorkbook.Sheets(1).Range(“A1:D13”) with Set Rng = ThisWorkbook.Sheets(1).UsedRange.

VBA to remove duplicates in an unknown range and unknown/dynamic columns

VBA to remove duplicates in an unknown range and unknown dynamic columnsWe are going to use the above data to remove duplicates in an unknown range and unknown columns. First of all we need to determine the range and in order to determine that we need last column and row count. Once we found that we can easily convert it as Range. After that we need to store the column count in a dynamic array. Pass this array value in the Column parameter of remove duplicates function.


The above code contains the number of columns as an array so it finds the duplicates by looking at the array values and Header value is xlNo so it considers the first row. You should note that we define the range from Cells(2, 1) or B1.

Recommended Excel VBA books

Excel 2016 Power Programming with VBA

Excel VBA 24-Hour Trainer

Excel VBA: A Beginners’ Guide

19 Comments