selecting first 500 numbers basis unique values in column A

[kshitij_dch]

I have 3 column


Column A has different City Name
Column B has name of Person
Column C has Phone numbers


Need a VBA code that can select city name that has phone numbers count greater than 500 , and delete number count that are greater than 500 ,


For example : City London has Phone number count 784 and city Totonto has phone number count 600


Need macro to delete 284 count from London city and 100 count from Toronto city and so on for different cities

 

[Fluff]

How about

Sub DeleteOver500()

   Dim Cl As Range
   Dim Rng As Range
   
   With CreateObject("scripting.dictionary")
      For Each Cl In Range("A2", Range("A" & Rows.Count).End(xlUp))
         If Not .exists(Cl.Value) Then
            .Add Cl.Value, 1
         ElseIf .Item(Cl.Value) < 500 Then
            .Item(Cl.Value) = .Item(Cl.Value) + 1
         Else
            If Rng Is Nothing Then
               Set Rng = Cl
            Else
               Set Rng = Union(Rng, Cl)
            End If
         End If
      Next Cl
   End With
   If Not Rng Is Nothing Then Rng.EntireRow.Delete
      
End Sub

 

[kshitij_dch]

This is working perfect ! Thanks

 

[Fluff]

Glad to help & thanks for the feedback