This code helps me to delete the entire row if it finds duplicates. I am using it for instance when I need to search duplicate incident tickets then I need to delete the entire row.

 

When I use the macro ?

To delete entire rows in which there are duplicate.

 

How to create the macro ?

Read How to create, edit, hide and select a macro in an excel report

 

How to create the button to associate it with the macro ?

Read How to create a button and associated it to a macro in an excel report

 

How is the macro ?

Copy the code below and paste it into your macro. You will see my comments in green if exist so follow the help to adapt to your need. Those 3 solutions work if my data is not into a table. This one is selecting a specific range:

Sub test()
' change A1:C10 by your range and change 1 by the column number of where to look for duplicate ie the column A is 1
Range(“A1:C10”).RemoveDuplicates Columns:=1, Header:=xlYes
End Sub

This one is selecting all range of data automatically:

Sub test()
' change 1 by the column number of where to look for duplicate ie the column A is 1
ActiveSheet.UsedRange.RemoveDuplicates Columns:=1, Header:=xlYes
End Sub

And the most coding one:

Sub test()
Dim i As Long
Dim j As Long
Dim k As Boolean
' starting to check from row 2
i = 2
Application.ScreenUpdating = False
Do While i<= ActiveSheet.UsedRange.Rows.Count
k = False
For j = i + 1 To ActiveSheet.UsedRange.Rows.Count
' change 1 by the column number of where to look for duplicate ie the column A is 1
If Cells(i, 1) = Cells(j, 1) Then
Rows(i).Delete
k = True
Exit For
End If
Next j
If Not k Then i = i + 1
Loop
Application.ScreenUpdating = True
End Sub

In the other hand, if I am using a table, this macro will not work. To delete duplicate inside a table, I use one of those 2. This is the simple one:

Sub test()
' change table1 by the name of your table and change 1 by the column number of where to look for duplicate ie the column A is 1
ActiveSheet.ListObjects("Table1").DataBodyRange.RemoveDuplicates Columns:=1, Header:=xlYes
End Sub

or

Sub test()
' change table1 by the name of your table and change 1 by the column number of where to look for duplicate ie the column A is 1
ActiveSheet.Range("Table1[#All]").RemoveDuplicates Columns:=1, Header:=xlYes
End Sub

And the most coding one:

Sub test()
Dim i As Range
' change table1 by the name of your table
Set i = ActiveSheet.ListObjects("Table1").Range
' change 1 by the column number of where to look for duplicate ie the column A is 1
i.RemoveDuplicates Columns:=1, Header:=xlYes
End Sub