Delete Row if It Equals "Delete"

[OilEconomist]

Hello and thanks in advance for your help and if it works, I will provide feedback.

I'm trying to delete the rows if the value in the cell in Column B is equal to delete, but I'm getting an error on

" If Cells(i, 2).Value = "Delete" Then Cells(i, 2).EntireRow.Delete"

Here is the code.

Sub DeleteRow()
        Dim i As Integer
        Dim LastRow2 As Long
             
        LastRow2 = Cells.Find(What:="*", _
        after:=Range("A1"), _
        LookAt:=xlPart, _
        LookIn:=xlFormulas, _
        SearchOrder:=xlByRows, _
        SearchDirection:=xlPrevious, _
        MatchCase:=False).Row


        
        For i = LastRow2 To 1 Step -1
        
            If Cells(i, 2).Value = "delete" Then Cells(i, 2).EntireRow.Delete
        
        Next i

End Sub()

 

[hiker95]

Thanks to everyone for their assistance, and apologies I could not get back sooner, but I was tied up with some other deadlines. Basically Post #7 and 8 solves the issue where i needed to be “long” versus an “integer” as there were over 200,000 rows of data.

Hiker 95 and MARK858, changing LastRow to long from integer worked as there were more than 200,000 rows.

Rick Rothstein, the error was on the line of code “For i = LastRow2 To 1 Step -1” which went away once I dimensioned i to long versus an integer. Also, it worked without using LCase, but thanks for that tip as I’m sure it will come in handy at some point.

Footoo, apologies for not mentioning you in Post #9 about what you had in Post #5 . I hadn’t tried your code at that point. When I did try the code, it deleted almost all the lines except a few which were lines that had numbers in that column. Anything with a word it deleted. Also I did not quite understand that code, but I’m going to keep it for future reference as you said it has no looping which probably means it’s more efficient. What you had in Post # 17 also is a workable solution.

Hiker95, I tried what you posted in Post 12, and although I did not quite understand it, it worked. I’m going to continue to study it because it seemed a bit faster. You had “Range(Addr) = Evaluate(Replace("IF(@=""delete"",""#N/A"",@)", "@", Addr))” twice. I tried it with it in once and twice and it worked both ways so I figured it was a typo. What if I wanted to extend the delete to Column C, how would I modify your code?

OilEconomist,

Thank you so much for your latest reply.

The macro code that I used is one of the GEMs that Rick Rothstein had created/displayed in one of his replies.

 

[hiker95]

OilEconomist,

What if I wanted to extend the delete to Column C, how would I modify your code?

With the same instructions as my reply #12, please try the following code.

Sub OilEconomist_V3()
' hiker95, 02/22/2019, ME1087704
Dim Addr As String
Application.ScreenUpdating = False
With Sheets("Sheet1")
  Addr = "C2:C" & Cells(Rows.Count, "C").End(xlUp).Row
  Range(Addr) = Evaluate(Replace("IF(@=""delete"",""#N/A"",@)", "@", Addr))
  On Error GoTo NoDeletes
  Columns("C").SpecialCells(xlConstants, xlErrors).EntireRow.Delete
End With
NoDeletes:
Application.ScreenUpdating = True
End Sub

 

[OilEconomist]

Thanks!

 

[MARK858]

Sub OilEconomist_V3()
' hiker95, 02/22/2019, ME1087704
Dim Addr As String
Application.ScreenUpdating = False
With Sheets("Sheet1")
  Addr = "C2:C" & Cells(Rows.Count, "C").End(xlUp).Row
  Range(Addr) = Evaluate(Replace("IF(@=""delete"",""#N/A"",@)", "@", Addr))
  On Error GoTo NoDeletes
  Columns("C").SpecialCells(xlConstants, xlErrors).EntireRow.Delete
End With
NoDeletes:
Application.ScreenUpdating = True
End Sub

@hiker95, I'm just a bit confused as why the With statement when you haven't qualified any of the Ranges/Cells/Columns? is it just an oversight?

 

[hiker95]

Thanks!

OilEconomist,

Thanks for the feedback. You are very welcome. Glad I could help. And, come back anytime.

 

[hiker95]

@hiker95, I'm just a bit confused as why the With statement when you haven't qualified any of the Ranges/Cells/Columns? is it just an oversight?

MARK858,

The above macro code is one the GEMs by Rick Rothstein.

And, the macro code does work correctly.

You may want to try the code out to see what it does, and, how fast it processes the data.

 

[MARK858]

I would rather understand how the With statement could work without linking to the ranges (and just because Rick wrote it that doesn't mean it is correct, he does very occasionally make omissions).

And, the macro code does work correctly

Make a blank sheet (say Sheet3) the activesheet, run the code, what happens to the rows in Sheet1 with delete in column C?

 

[MARK858]

Just some code in response to my own question (to get it working correctly from my point of view) as just about to get some kip.
The code needs more testing but no time now.

Sub OilEconomist_V3modified()
    Dim Addr As String
    Application.ScreenUpdating = False
    With Sheets("Sheet 1")
        Addr = "C2:C" & .Cells(Rows.Count, "C").End(xlUp).Row
        .Range(Addr) = Evaluate(Replace("IF(@=""delete"",""#N/A"",@)", "@", .Range(Addr).Address(, , , True)))
        On Error GoTo NoDeletes
        .Columns("C").SpecialCells(xlConstants, xlErrors).EntireRow.Delete
    End With
NoDeletes:
    Application.ScreenUpdating = True
End Sub

I am sure if Rick see's this he will write it tidier as he is much better with Evaluate than I am

Please note that I do understand the issue that footoo states about if there are formulas and the code above does not address it.