deleting rows with specific criteria

[jbmahoney]

I am trying to delete rows in a spreadsheet which have 'other - rubbish bin' in column S. Whenever I use the following scrpit, it deletes the heading row (row A). How can I get round this and is there a better way to get rid of all rows with this criteria in?

Sub breaktest()
'
' breaktest Macro
' Macro recorded 12/10/2006 by John Royle

Dim lastrow As Long

Sheets("BREAKS>$1m").Select
Selection.AutoFilter field:=19, Criteria1:="other - rubbish bin"
Selection.SpecialCells(xlCellTypeVisible).Select
Selection.Delete Shift:=xlUp
Selection.AutoFilter field:=19
Rows("2:21575").Select

End Sub

 

[pgc01]

Hi John

One possibility is to filter atarting in row 2.

Please try:

Sub breaktest()
Dim rRng As Range

With Sheets("BREAKS>$1m")
    Set rRng = .Range("S2", .Range("S" & .Rows.Count).End(xlUp))
    rRng.AutoFilter field:=1, Criteria1:="other - rubbish bin"
    rRng.SpecialCells(xlCellTypeVisible).Delete Shift:=xlUp
    .AutoFilterMode = False
End With
End Sub

Hope this helps
PGC

 

[sykes]

Hello my 'ansom

This should do what you wish:

Sub breaktest()
Dim i As Integer
i = Range("S65536").End(xlUp).Row
For Each c In sheets("BREAKS>$1m").Range("S2:S" & i)
    If c.Value = "other - rubbish bin" Then
        c.EntireRow.Delete
    End If
Next
End Sub

All the best

d'rectly.....

 

[pgc01]

Hi sykes

When you want to delete rows it's better to start at the last row and go up. Your loop will miss some rows.

Try this simple test. Write in S2 and S3 "other - rubbish bin" and in S4 some other thing. Run your code and check that it missed one of the rows.

Also a loop is much less efficient than autofilter. I tried the codes with 10000 rows. The one with the loop took almost 30s (maybe my PC isn't the fastest). The one with the autofilter took less than 1s. Please try it. It will make you a fan of autofilter. I am.

Best regards
PGC

 

[jbmahoney]

thanks you all, that has been a great help.