Delete a row if unique event occurs only once

[robertpri]

We have 1,000's of rows of text data, but a huge number of rows have only one unique event.

joe called
joe called
bill called
joe called

etc. It's almost impossible to sort this data, so we're trying to find a command or macro that would delete the entire row when "bill called" because it only happened once. This scrubs our data to indicate repeat events only.

And this is always text-never numbers

Much appreciated!

 

[martinee]

Assuming the text values are identical, this should do it:

Sub delunique()
Dim rng As Range, c As Range

Set rng = Range("A2:A" & Range("A65536").End(xlUp).Row)

For i = rng.Count + 1 To 2 Step -1
Set c = Cells(i, 1)

If Application.WorksheetFunction.CountIf(rng, c.Value) = 1 Then
    c.EntireRow.Delete
End If
Next i

End Sub

HTH.

 

[robertpri]

OMG!
I am SO impressed! I don't pretend to understand it, but it works!
I could have messed with this forever and never got it.
You have no idea how much work you have saved me.

VERY much appreciated--thank you!

robertpri