Excel Macro Help: Deleting duplicates?
Is there a way to delete duplicate entries PLUS the original? I have a list of email addresses and want to delete the unsubscribers. So what I had planned was to paste the unsubscribers under the original list and filter by unique records. However, that method would still include the unsubscriber. So is there a way to delete the duplicate and original? Is there an alternative method I can use?
Answers & Comments
Verified answer
Here is a fairly simple macro that will delete all instances of a duplicated entry, regardless of how many times it is duplicated.
Since you did not specify, I am inferring that your email addresses are in a column. The following example evaluates column A as the data column. It will delete the entire row for each duplicated value.
If your column is not 'A', modify the code first:
Change the five "A" references to your column letter, i.e. "C", "G", etc
Change the "A:A" reference to your column letter, i.e. "C:C", "G:G", etc
Then copy the macro, modified as required, to the clipboard:
Sub Delete_All_Dups()
Dim i, LastRow
LastRow = Range("A" & Rows.Count).End(xlUp).Row
Application.ScreenUpdating = False
For i = 1 To LastRow
If Application.CountIf(Range("A:A"), Cells(i, "A").Value) > 1 Then
Cells(i, "A").Interior.ColorIndex = 37
End If
Next
For i = LastRow To 1 Step -1
If Cells(i, "A").Interior.ColorIndex = 37 Then
Cells(i, "A").EntireRow.Delete
End If
Next
End Sub
Press ALT + F11
In the menus at the top of the VBE, select INSERT > MODULE
Paste the macro into the white editing area to the right.
Still in the VBE, go to Tools > Macros.
Select this macro and click 'RUN'.