Find DUP, remove both same entries

[joelle]

Dear Experts,

I guess there were similar posts as this but time is almost never on my side
so some quicky help is so much appreciated:

In the attached example, real life is that I have 100K rows, I need to find duplicate email, then remove both the entries, I mean, if I find a dup for anna@abc.com, remove the entire row and also the other row that contains anna@abc.com.

I know how to filter and remove dups, but it leaves me with 1 unique anna@abc.com, I want both gone.

So,
1. what is an plain excel way (like use filter to find dups, blah blah), or,
2. a simple VBA, but I do prefer the plain excel way.

Thank you so much!

joelle

[mdmackillop]

In a blank column =COUNTIF(A:A,A1) and fill down, Filter on numbers > 1, select and delete rows.

[mdmackillop]

as VBA

Sub Test()
  Dim r As Range
    Set r = Range(Cells(1, 1), Cells(Rows.Count, 1).End(xlUp))
    With r.Offset(, 10)
        .FormulaR1C1 = "=COUNTIF(C[-10],RC[-10])"
        .AutoFilter Field:=1, Criteria1:=">1"
        .Offset(1).SpecialCells(xlCellTypeVisible).EntireRow.Delete
        .AutoFilter
        .ClearContents
    End With
End Sub

[joelle]

Hi mdmackillop

Sorry for the delay but my pc was down for days
please see my 2nd reply. thank you.

cheers

[joelle]

Hi
I edited my 2nd reply
The VBA codes work beautifully although I have not tried for 100K rows
I still would like to see how the formula works that allows me to remove all the "2" when after filtered they are consecutive rows, so that it makes less time to remove could be 3,000 rows of dups or so.

or else, I will use the vba codes, will try with 100K rows and will post back - Thank you so Much!!!