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

 

[MARK858]

So your thinking he wants to search the entire sheet for these values

No the OP is searching the whole sheet, I don't think they either want or need to search all 17 billion cells (depending on the interpretation of a billion) which is why I made post number 6.

If it is every used cell with values in the sheet they can try...

Sub delarr2()

    Dim rFnd As Range, rDel As Range, lr As Long, lc As Long
    Dim Addr1 As String, vList As Variant, lCtr As Long

    Application.ScreenUpdating = False
    
    vList = VBA.Array("Recover", "NR")
    
    lr = Cells.Find("*", , xlValues, , xlByRows, xlPrevious).Row
    lc = Cells.Find("*", , xlValues, , xlByColumns, xlPrevious).Row
    
    For lCtr = LBound(vList) To UBound(vList)
        With Range("A1", Cells(lr, lc))
            Set rFnd = .Find(vList(lCtr), .Cells(.Rows.Count, .Columns.Count), xlValues, xlWhole, xlByRows, xlNext, True)
         
            If Not rFnd Is Nothing Then
                If rDel Is Nothing Then
                Set rDel = rFnd
                Else
                    Set rDel = Application.Union(rDel, rFnd)
                End If
                
                Addr1 = rFnd.Address
                Set rFnd = .FindNext(After:=rFnd)
                
                Do Until rFnd.Address = Addr1
                    Set rDel = Application.Union(rDel, rFnd)
                    Set rFnd = .FindNext(After:=rFnd)
                Loop
            End If
        End With
    Next lCtr
    
    If Not rDel Is Nothing Then rDel.EntireRow.Delete

    Application.ScreenUpdating = True

End Sub

Which I have currently set as case sensitive and the criteria are the only things in the cells (but it would be much better to narrow the range down further).

 

[My Aswer Is This]

I would use this script if he want's to search entire usedrange

But there are I'm sure other ways that may be faster but I don't those ways.

Sub Search_Sheet()
Application.ScreenUpdating = False
Dim c As Range
    For Each c In ActiveSheet.UsedRange
        If c.Value = "Recover" Or c.Value = "NR" Then c.EntireRow.Delete
    Next
Application.ScreenUpdating = True
End Sub

 

[MARK858]

I should think that the (I think Ricks) method below (untested) should be reasonably quick over large data sets if using Excel 2010 or later and I don't think has the size of array issue (and doesn't have the typo) that the code I posted earlier had.

Sub Delit()
    Dim V As Variant, DeleteMe As Variant
    Dim lr As Long, lc As Long
    
    DeleteMe = Array("Recover", "NR")

    lr = Cells.Find("*", , xlValues, , xlByRows, xlPrevious).Row
    lc = Cells.Find("*", , xlValues, , xlByColumns, xlPrevious).Column

    For Each V In DeleteMe
        Range("A1", Cells(lr, lc)).Replace "*" & V & "*", "#N/A", xlWhole, , True, False, False
    Next
    On Error Resume Next
    Range("A1", Cells(lr, lc)).SpecialCells(xlConstants, xlErrors).EntireRow.Delete
    On Error GoTo 0
    
End Sub

 

[JLGWhiz]

See if this will work

Sub t()
ActiveSheet.Columns("A").Insert
For Each c In Intersect(Range("B:B"), ActiveSheet.UsedRange)
    If Application.CountIf(c.Resize(1, ActiveSheet.UsedRange.Columns.Count), "Recover") > 0 Then
        c.Offset(, -1) = "x"
    End If
Next
ActiveSheet.Range("A:A").SpecialCells(xlCellTypeConstants).EntireRow.Delete
Columns(1).Delete
End Sub

 

[hamohd70]

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


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

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

 

[Rick Rothstein]

I should think that the (I think Ricks) method below (untested) should be reasonably quick over large data sets if using Excel 2010 or later and I don't think has the size of array issue (and doesn't have the typo) that the code I posted earlier had.

Sub Delit()
    Dim V As Variant, DeleteMe As Variant
    Dim lr As Long, lc As Long
    
    DeleteMe = Array("Recover", "NR")

    lr = Cells.Find("*", , xlValues, , xlByRows, xlPrevious).Row
    lc = Cells.Find("*", , xlValues, , xlByColumns, xlPrevious).Column

    For Each V In DeleteMe
        Range("A1", Cells(lr, lc)).Replace "*" & V & "*", "#N/A", xlWhole, , True, False, False
    Next
    On Error Resume Next
    [B][COLOR="#FF0000"]Range("A1", Cells(lr, lc)).SpecialCells(xlConstants, xlErrors).EntireRow.Delete[/COLOR][/B]
    On Error GoTo 0
    
End Sub

Yeah, the above is based on code I have posted earlier. Given this has to apply to all the cells in the used range, there is one possibility where the code, as written, won't work correctly and that is when the word "Recover" or "NR" appears more than once on a single row. Then the EntireRow for each of them will produce an overlapping range which Excel won't let you delete (you On Error Resume Next will hide the error that would get generated, so you would have to comment it out to verify what I am saying). There is a way around this though... simply intersect the EntireRow with a single column and delete that intersection. To do that, replace the red highlighted line of code with this...

[table="width: 500"]
[tr]
	[td]Intersect(Columns("A"), Range("A1", Cells(lr, lc)).SpecialCells(xlConstants, xlErrors).EntireRow).Delete[/td]
[/tr]
[/table]

EDIT NOTE
---------------------------
I see the OP has indicated the search range is a single column, my caution does not apply here; however, you may want to take note of it for possible future use of code based on my original replace/delete concept.

 

[Rick Rothstein]

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

Sub Filter_Me()
Application.ScreenUpdating = False
    [B][COLOR="#FF0000"]With ActiveSheet.Range(Cells(1, 3), Cells(Cells(Rows.Count, "C").End(xlUp).Row, 3))[/COLOR][/B]
        .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

For your consideration...

This is a less complicated way to write the line of code I highlighted in red above and would be easier for the OP to change when your guess at the column to parse was incorrect...

With Intersect(Columns("C"), ActiveSheet.UsedRange)

 

[hamohd70]

let me first apologize for the confusion I made to all of you.

this code

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

is looking for the first instance and returning the range. the words searched for can be in any column of a row.

the code works just fine for lines up to few 500 lines but gets slower as the number increases and gets stuck with larger ones.

@M.A.I.T your code here worked fine on my 97200 lines.

@MARK858 your code in post#11 gave my error 400 ! thanks anyway

 

[Rick Rothstein]

@MARK858 your code in post#11 gave my error 400 ! thanks anyway

Mark858 noted in Message #13 that the code he posted in Message #11 had a typo. The new code he posted in Message #13 should work for you also but with one caution. If the word you are searching for can appear more than once on any single row, then you would need to make the change I pointed out in Message #17 in order to make it work correctly for that situation.

 

[MARK858]

Mark858 noted in Message #13 that the code he posted in Message #11 had a typo.

...and wasn't worth correcting until the OP clarified the ranges. Not worth me altering now as Autofilter should be faster over a single column as will the replace with error method (although I would now still restrict it to the single column range).

I was also tempted to try an amended version of JLGWhiz's method, adding a sort on column A before the delete (as the delete was slowing down the code when testing on various methods).

@ Rick, thanks for the info on your code, much appreciated.