Deleting ALL duplicates using Pearson's code
 

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

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

" wrote:

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

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


wrote in message
oups.com...
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