We use Remove Duplicates feature of excel application to remove duplicate data in a column. Same task we can do by using VBA code. Range.RemoveDuplicates method of exvel VBA helps you to do this.
We use Remove Duplicates feature of excel application to remove duplicate data in a column. Same task we can do by using VBA code. Range.RemoveDuplicates method of excel VBA helps you to do this.
Syntax:
Expression.RemoveDuplicates(Columns,Header)
Expression represents the Range object.
Columns: This parameter is required. This specifies the column that contain duplicate value.
Header: This parameter is optional. This specifies whether data has header row or not. It’s values can be xlNo, xlYes, xlYesNoGuess.
xlNo: It is the default value for Header parameter. It specifies that data does not contain header row.
xlYes: It specifies that data contains header row.
xlYesNoGuess: xlGuess is used when you want Excel to attempt to determine the header.
VBA Remove Duplicate Examples on Sample Data
We have the below given sample data which contains duplicate values.
Remove Duplicate Values from One Column
Range(“A1:C11”).RemoveDuplicates Columns:=2, Header:=xlYesWhen you run this code, your data will look like this.
All the rows appearing with duplicate values in column B has been removed and rows with unique values appearing above in data range are remaining.
However, if you see that values in column B are repeating but with different dates in column A so with combination of A and B there are less duplicate values. To remove duplicate values on combination of multiple columns we use Array method.
Remove Duplicate Values Based from Multiple Column
Range(“A1:C11”).RemoveDuplicates Columns:= Array(1, 2), Header:=xlYesThis Array tells VBA to compare the data using both columns 1 and 2 (A and B) for duplicity. When you run this code, your data will look like this.
This code example uses all three columns to check for duplicates.
Range(“A1:C11”).RemoveDuplicates Columns:= Array(1, 2,3), Header:=xlYes