Best way to find and delete rows !

[hamohd70]

I have a sheet that contains at least 97000 rows. I'm testing a VBA code to find and delete rows that contain certain word. my problem is that this works fine with fewer number of rows but gets stuck with larger ones.

here is my code:

    Application.ScreenUpdating = False
    Application.StatusBar = "Cleaning up, please wait.."

    Do While True
        Set c = Cells.Find(What:="Recover")
        On Error Resume Next
        If c Is Nothing Then Exit Do
        c.EntireRow.Delete
    Loop

    Application.ScreenUpdating = True
    Application.StatusBar = False

Is there a better and faster way to do it?

what if I wanted to combine conditions like deleting rows that contain either "Recover" or "NR"?

thanks

 

[hamohd70]

@MARK858 and @Rick Rothstein: each of the searched words will appear only once in each row and normally in one column across the sheet. by testing code #13 https://www.mrexcel.com/forum/members/rick-rothstein.htmlon my 97200 rows I found it too slow and then excel just crashed. I think that using a loop for larger row is not really efficient.

 

[Rick Rothstein]

@MARK858 and @Rick Rothstein: each of the searched words will appear only once in each row and normally in one column across the sheet. by testing code [URL=https://www.mrexcel.com/forum/usertag.php?do=list&action=hash&hash=13]#13 [/URL] https://www.mrexcel.com/forum/members/rick-rothstein.htmlon my 97200 rows I found it too slow and then excel just crashed. I think that using a loop for larger row is not really efficient.

The loop only iterates twice because Mark set it up to search for two words... "Recover" and "NR" (although I am not sure where the NR came from). Given that, I am a little surprised you find the code in Message #13 too slow. Just out of curiosity, how many columns maximum are there on your sheet?

 

[hamohd70]

The loop only iterates twice because Mark set it up to search for two words... "Recover" and "NR" (although I am not sure where the NR came from). Given that, I am a little surprised you find the code in Message #13 too slow. Just out of curiosity, how many columns maximum are there on your sheet?

original sheet has 22 columns.
"NR" is short for "Normal"

the code#8 worked in 8 minutes!!

 

[MARK858]

"Recover" and "NR" (although I am not sure where the NR came from)

Bottom of post number 1

what if I wanted to combine conditions like deleting rows that contain either "Recover" or "NR"?

thanks

Btw, I just have a feeling that the speed is an issue with the number of non-contiguous cells (although I thought that was sorted for specialcells from 2010).

Just guessing as the replace part worked fast when I tested it (possible option to use sort?) although was a fair bit quicker when restricted to one column now we know that is the case.

Just in case you miss it the OP has responded in the previous post to this.

 

[My Aswer Is This]

Now that we know the search range is column "B"
This is the way my original script should be.
I believe the OP has already used my script and said it worked so he/she must know how to modify it.

Sub Filter_Me()
'Modified 11-4-2017 9:29 PM EDT
Application.ScreenUpdating = False
On Error GoTo M
    With ActiveSheet.Range(Cells(1, 2), Cells(Cells(Rows.Count, 2).End(xlUp).Row, 2))
        .AutoFilter Field:=1, Criteria1:="Recover", Operator:=xlOr, Criteria2:="NR"
        .Offset(1).Resize(.Rows.Count - 1).SpecialCells(xlCellTypeVisible).EntireRow.Delete
        .AutoFilter
    End With
    Exit Sub
Application.ScreenUpdating = True
M:
    MsgBox "No rows found with this value" & vbNewLine & "Or some other problem occured"
ActiveSheet.AutoFilterMode = False
Application.ScreenUpdating = True
End Sub

 

[MARK858]

Now that we know the search range is column "B"

the words will only be in one column, column D. My example has 97200 rows.

 

[My Aswer Is This]

Yes Mark I'm wrong again.
I could make some excuse up but I guess it boils down to I'm just crazy.

Now that we know the search range is column "D"
This is the way my original script should be.
I believe the OP has already used my script and said it worked so he/she must know how to modify it.

Sub Filter_Me()
'Modified 11-4-2017 10:51 PM EDT
Application.ScreenUpdating = False
On Error GoTo M
    With ActiveSheet.Range(Cells(1, 4), Cells(Cells(Rows.Count, 4).End(xlUp).Row, 4))
        .AutoFilter Field:=1, Criteria1:="Recover", Operator:=xlOr, Criteria2:="NR"
        .Offset(1).Resize(.Rows.Count - 1).SpecialCells(xlCellTypeVisible).EntireRow.Delete
        .AutoFilter
    End With
    Exit Sub
Application.ScreenUpdating = True
M:
    MsgBox "No rows found with this value" & vbNewLine & "Or some other problem occured"
ActiveSheet.AutoFilterMode = False
Application.ScreenUpdating = True
End Sub