VBA delete the first 100 duplicates

[Mr Tran]

Hi,

I need a VBA code to delete the first 100 duplicates in column A and keep the remaining. There could be a thousand names that appear a thousand times, but would want to delete the first 100 duplicates that their name appear on this list and keep the remaining.

Thanks,

Mr Tran

 

[mikerickson]

Something like

Dim DelRange as Range
Dim DelCount as Long
Dim OneCell as Range

With Range("A:A")
    Set DelRange = .Cells(Rows.Count, 2)
    For Each oneCell in Range(.Cells(1,1), .Cells(Rows.Count,1).End(xlup))
        If WorksheetFunction.CountIf(oneCell, .Cells) <> 1 Then
            DelCount = DelCount + 1
            Set DelRange = Application.Union(DelRange, oneCell)
        End If
        If DelCount >= 100 Then Exit For
    Next oneCell

    On Error Resume Next
    Application.Intersect(DelRange, .Cells).Delete shift:=xlUp
    On Error Goto 0 
End With

 

[Mr Tran]

Thank you mikerickson for the response. I get a Run-time error '13'; Type mismatch


If WorksheetFunction.CountIf(OneCell, .Cells) <> 1 Then

any help resolving this is very much appreciated.


Thanks!

Mr Tran

 

[mikerickson]

I got the arguments backwards

If WorksheetFunction.CountIf(.Cells, oneCell.Value) <> 1 Then

 

[Mr Tran]

The code is great but I don't think I was clear. I was looking for something that would delete the first 100 duplicates of each subset of names. If Mary appear 1000 times, Jim appears 900 time and John appear 800 times, the macro would remove 100 duplicates from each.

Thanks!

Mr Tran

 

[Mr Tran]

It would also delete the entire row

 

[mikerickson]

What if Mary appears 99 times, delete none or delete 98?

 

[Mr Tran]

Sorry, i phrased the it wrong. I should have said to delete the first 100 instances, so in that situation of Mary appearing 99 times, it would delete all 99

 

[mikerickson]

If Mary appears 99 times, delete all of them so Mary is not in the result at all?
Or leave one Mary in the result?

 

[Mr Tran]

it would delete all of them so Mary would no longer be on the list