Deleting ALL duplicates using Pearson's code

[mike.wilson8]

On this site, http://www.cpearson.com/excel/deleting.htm, Pearson gives
an example on how to delete duplicate rows. Does anyone know how the
code can be changed to delete ALL rows.

Ex -
Column A
1--delete
2--delete
3
1--delete
4
5
6
2--delete

** I would like the code to delete all the 1s and 2s.

Thank you
Mike

 

[Peo Sjoblom]

Wouldn't it be simpler applying an autofilter, filter on 1 and 2, select
range, press F5, special and select visible cell (make sure header is not
included), then just press Ctrl - (minus/dash) and delete entire row


Regards,

Peo Sjoblom

 

[Bernie Deitrick]

Mike,

Try the code below.

HTH,
Bernie
MS Excel MVP

Sub Delete1sAnd2s()
Dim myRows As Long
Range("A1").EntireColumn.Insert
Range("A1").FormulaR1C1 = _
"=IF(OR(RC[1]=1,RC[1]=2),""Trash"",""Keep"")"
myRows = ActiveSheet.UsedRange.Rows.Count
Range("A1").Copy Range("A1:A" & myRows)
With Range(Range("A1"), Range("A1").End(xlDown))
.Copy
.PasteSpecial Paste:=xlValues
End With
Cells.Select
Selection.Sort Key1:=Range("A1"), Order1:=xlAscending
Columns("A:A").Find(What:="Trash", After:=Range("A1")).Select
Range(Selection, Selection.End(xlDown)).EntireRow.Delete
Range("A1").EntireColumn.Delete
End Sub