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]

Rick Rothstein, Mark858, and Hiker95 much thanks for all your help this far.

I will post the error once I get a chance to work on the file again.

Hiker95 I’ll try your suggestion.

Mark858, I will try your suggestion of posting a modified file if what Hiker95 suggests doesn’t work.

OilEconomist,

You are very welcome, and, we look forward to your next reply.

 

[hiker95]

OilEconomist,

We have not seen your actual raw data workbook/worksheet.

With your raw data in worksheet Sheet1 (you can change the macro to run in a different worksheet name).

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

Please test the above macro in a copy of your actual raw data workbook.

 

[hiker95]

OilEconomist,

The above macro in reply #12 does not do any looping in the rows in column B.

And, should be very fast.

 

[footoo]

OilEconomist,

The above macro in reply #12 does not do any looping in the rows in column B.

And, should be very fast.

It is the same approach as the macro per post # 5.

Except : If there are any formulas in column B, the post # 12 macro will convert all to values, the post # 5 macro will retain any formulas.

 

[hiker95]

OilEconomist,

I have made a change to the original macro after doing so more testing.

With your raw data in worksheet Sheet1 (you can change the macro to run in a different worksheet name).

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

Please test the above macro in a copy of your actual raw data workbook.

 

[Michael M]

@footoo....obviously your code was too efficient and sent 'em all into shock !!!

Sub DeleteRow()
On Error Resume Next
With [B:B]
    .Replace What:="delete", Replacement:="#N/A!", LookAt:=xlPart, _
        SearchOrder:=xlByRows, MatchCase:=False, SearchFormat:=False, _
        ReplaceFormat:=False
    .SpecialCells(xlCellTypeConstants, 18).EntireRow.Delete
End With
On Error GoTo 0
End Sub

 

[footoo]

If column B contains formulas and "Delete" is the result of a formula, alternative code could be based on :
• Filter for "delete"
• Go to SpecialCells/Visible
• Delete entire row
• Remove filter

 

[OilEconomist]

Thanks everyone. I will respond with a more detailed message about the other alternatives, but I wanted to at least let everyone know that dimensionalizing i to long worked. Thanks so much!

More details to come on the other solutions.

 

[hiker95]

Thanks everyone. I will respond with a more detailed message about the other alternatives, but I wanted to at least let everyone know that dimensionalizing i to long worked. Thanks so much!

More details to come on the other solutions.

OilEconomist,

I am glad that I was able to help you with my reply #8.

 

[OilEconomist]

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?