Remove duplicate rows by VBA Excel | debugvba.com

remove duplicate vba excel

Objective

To remove duplicate rows using VBA Excel.

Approach

In this VBA Excel automation, we shall remove duplicate rows from excel worksheet. We have defined a range where we have duplicate records, and then we have used RemoveDuplicates method to find unique records.We have chosen all 3 columns (1,2,3) in RemoveDuplicates method. We can also select one or more columns to remove duplicates.


Removing duplicates based on all the columns.

As we can see in the code below that we can remove the duplicates by checking all the columns. It means that if there are 2 or more identical rows, then only one row will be left and all other rows will be deleted.

Sub RemoveDuplicatesAllColumn()
Dim SampleRange As Range
Dim KeyColumns As Variant

'select the range where data exist
Set SampleRange = Range("A1:C10")

'Set the arry size to maximum number of columns
TotalColumns = SampleRange.Columns.Count
ReDim KeyColumns(0 To TotalColumns - 1)

'Assign values to each array element
For i = 0 To TotalColumns - 1
KeyColumns(i) = i + 1
Next i

'Remove the duplicates based on all columns
SampleRange.RemoveDuplicates Columns:=(KeyColumns), Header:=xlYes

End Sub

 

As we can see in the image below that there are duplicate records for USA,i.e.  in row 2 and 3.Once we execute the VBA code 1 duplicate row will be deleted. We can also remove duplicates based on one or more column.

 

remove duplicate vba excel

 

Removing duplicates based on single column

 

Sub RemoveDupliatesSingleColumn()
Dim SampleRange As Range
Set SampleRange = Range("A1: C10")
'Remove duplictaes based on column 1
SampleRange.RemoveDuplicates Columns:=Array(1), Header:=xlYes
End Sub

 

Here we wanted to remove duplicate ID, here ID 1 was appearing twice, we can see the same in the image below. We have removed second row which had 1 as ID and “New York” as city.

 

remove duplicate vba excel

 

Removing duplicates based on specific columns

 

In this case whichever columns we shall mention in column array, those columns will act as key columns,and accordingly duplicates will be removed.Here we have considered column 1 and 2 as key column.So any rows which repeat values of column 1 and 2 ,will be considered as duplicate, and they will be deleted.

 

Sub RemoveDupliatesSpecificColumn()
Dim SampleRange As Range
Set SampleRange = Range("A1: C10")
'Remove duplictaes based on column 1,2
SampleRange.RemoveDuplicates Columns:=Array(1, 2), Header:=xlYes
End Sub

 

1057_Image_7

 

 

Reference

https://docs.microsoft.com/en-us/office/vba/api/excel.range.removeduplicates

Post you may like

VBA Excel | Autofit , Merge ,Row Height , Column Width ,Wrap text properties