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.
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.
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