Macro to delete row based on column A

[robrobet]

HI,

I have a sheet with thousands entry and I want a macro that looks column A (which is ID number) if macro find a match than delete all of those rows.
example Sheet 1,
Column A
8888 ABC
8888 ABC
7777 ABC
8888 ABC

When macro finish I should only see the row with 7777ABC.
Thanks in Advance.

 

[Prasad K]

HI,

I have a sheet with thousands entry and I want a macro that looks column A (which is ID number) if macro find a match than delete all of those rows.
example Sheet 1,
Column A
8888 ABC
8888 ABC
7777 ABC
8888 ABC

When macro finish I should only see the row with 7777ABC.
Thanks in Advance.

Try this
Is this helpful to you or not

Sub Duplicate()

    Dim ws As Worksheet
    Dim rCheck As Range
    Dim rDel As Range

    Set ws = ActiveWorkbook.ActiveSheet

    For Each rCheck In ws.Range("A1", ws.Cells(ws.Rows.Count, "A").End(xlUp)).Cells
        If WorksheetFunction.CountIf(ws.Columns("A"), rCheck.Value) > 1 Then
            If Not rDel Is Nothing Then
                Set rDel = Union(rDel, rCheck)
            Else
                Set rDel = rCheck
            End If
        End If
    Next rCheck

    If Not rDel Is Nothing Then rDel.EntireRow.Delete

End Sub

 

[Peter_SSs]

You could also try this with a copy of your workbook.
If it takes too long post back for a longer code but faster execution.

Sub DeleteDupes()
  Application.ScreenUpdating = False
  With Range("A1", Range("A" & Rows.Count).End(xlUp))
    .Value = Evaluate(Replace("if(countif(@,@)>1,""#N/A"",@)", "@", .Address))
    On Error Resume Next
    .SpecialCells(xlConstants, xlErrors).EntireRow.Delete
    On Error GoTo 0
  End With
  Application.ScreenUpdating = True
End Sub

 

[Marc L]

Hi, a VBA demonstration which should be fast enough :​

Sub Demo1()
    Dim S&
        Application.ScreenUpdating = False
    With [A1].CurrentRegion.Columns
       .Item(.Count + 1).Formula = "=--(COUNTIF(" & .Item(1).Address & ",A1)>1)"
        S = Application.Sum(.Item(.Count + 1))
     If S Then
       .Resize(, .Count + 1).Sort .Item(.Count + 1), 1
       .Rows(.Rows.Count - S + 1 & ":" & .Rows.Count).Clear
     End If
       .Item(.Count + 1).Clear
    End With
        Application.ScreenUpdating = True
End Sub

 

[robrobet]

Thanks everyone for your Help.

 

[Peter_SSs]

You're welcome. Glad we could help. Thanks for the follow-up.