Remove ALL duplicates from column A in Excel

Question

I am looking for a macro that can remove ALL duplicates from column A.

Input:

John
Jimmy
Brenda
Brenda
Tom
Tom
Todd

Output:

John
Jimmy
Todd

I am working with a large set of data, and Excel isn't cooperating. Can't seem to find a solution online that works.

Thanks!

Answer 1

When you want to de-duplicate your list, that is make sure you only have ONE item left of each, you can to this:

In Excel 2007 and above you have a Remove Duplicates in the Data menu, which will do it for you.

In Excel 2003 and earlier you can use the Advanced Filter in the Data/Filter menu:

And then copy-paste the results in a new sheet.

You can see the full procedure here.

Otherwise it is a tedious macro to write (a recursive loop to check if the value exist in the set). It can be done, but do you really need it?

But if you want to actually delete all entries that are the same then using @Eoins's macro will do the job, but a bit modified as follows:

Option Explicit

Sub DeleteDuplicate()
    Dim x, Y As Long
    Dim LastRow As Long
    Dim myCell As String
    LastRow = Range("A1").SpecialCells(xlLastCell).Row
    For x = LastRow To 1 Step -1
        myCell = Range("A" & x).Text
        If Application.WorksheetFunction.CountIf(Range("A1:A" & x), myCell) > 1 Then
            For Y = x To 1 Step -1
                If Range("A" & Y).Text = myCell Then
                    Range("A" & Y).EntireRow.Delete
                End If
            Next Y
        End If
    Next x
End Sub