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

 

[paulxmw]

'Filter out Recover
ActiveSheet.Range("$A$1:$Y$15000").AutoFilter Field:=3, Criteria1:=Array( "Recover"), Operator:= _
xlFilterValues
Range("A2:I197000").SpecialCells(xlCellTypeVisible).EntireRow.Delete 'This delets the range
ActiveSheet.ShowAllData

 

[My Aswer Is This]

Try this:
Will delete any row that has "Recover" or "NR" in column "C"

Sub Filter_Me()
Application.ScreenUpdating = False
    With ActiveSheet.Range(Cells(1, 3), Cells(Cells(Rows.Count, "C").End(xlUp).Row, 3))
        .AutoFilter Field:=1, Criteria1:="Recover", Operator:=xlOr, Criteria2:="NR"
        .Offset(1).Resize(.Rows.Count - 1).SpecialCells(xlCellTypeVisible).EntireRow.Delete
        .AutoFilter
    End With
Application.ScreenUpdating = True
End Sub

 

[hamohd70]

Try this:
Will delete any row that has "Recover" or "NR" in column "C"

Sub Filter_Me()
Application.ScreenUpdating = False
    With ActiveSheet.Range(Cells(1, 3), Cells(Cells(Rows.Count, "C").End(xlUp).Row, 3))
        .AutoFilter Field:=1, Criteria1:="Recover", Operator:=xlOr, Criteria2:="NR"
        .Offset(1).Resize(.Rows.Count - 1).SpecialCells(xlCellTypeVisible).EntireRow.Delete
        .AutoFilter
    End With
Application.ScreenUpdating = True
End Sub

Thanks but it did not work!! I received error 400 but the range was auto filtered.

 

[hamohd70]

'Filter out Recover
ActiveSheet.Range("$A$1:$Y$15000").AutoFilter Field:=3, Criteria1:=Array( "Recover"), Operator:= _
xlFilterValues
Range("A2:I197000").SpecialCells(xlCellTypeVisible).EntireRow.Delete 'This delets the range
ActiveSheet.ShowAllData

... did not work

 

[MARK858]

@hamohd70, are you really trying to search all the cells on the worksheet (17,179,869,184 cells)?

Set c = Cells.Find(What:="Recover")

What is the actual range/column you want searched?

If you are going to use the Find method then loop it in memory then delete the rows in one go

 

[My Aswer Is This]

So your saying if did delete the rows but got an error?
Is it true we want to search column (3) for "Recover" or "NR"
I test all my scripts.

Thanks but it did not work!! I received error 400 but the range was auto filtered.

 

[My Aswer Is This]

Try this:

When two of us provide you with a script and all you say is they do not work you may need to explain more what you want.
Are you searching for: "Recover" or "NR" in column (3)?
Now these values must be exact.
I test all my scripts.

Sub Filter_Me()
'Modified 11-4-2017 11:01 AM EDT
Application.ScreenUpdating = False
On Error GoTo M
    With ActiveSheet.Range(Cells(1, 3), Cells(Cells(Rows.Count, "C").End(xlUp).Row, 3))
        .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]

@ M.A.I.T. the OP has never stated it was column 3, it comes from post number 2 by paulxmw and the code the OP posted in post number 1 refers to all the cells on the worksheet

 

[My Aswer Is This]

I still wondering that myself.
In his post he said:
Set c = Cells.Find(What:="Recover")
Not sure what that means.

So your thinking he wants to search the entire sheet for these values
That's why I asked.

I do not know how to do a auto filter on all columns in the usedrange

@ M.A.I.T. the OP has never stated it was column 3, it comes from post number 2 by paulxmw and the code the OP posted in post number 1 refers to all the cells on the worksheet