Remove duplicate rows by VBA Excel |

remove duplicate vba excel


To remove duplicate rows using VBA Excel.


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






Post you may like

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