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.
Table of Contents
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
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
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
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
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