Delete Row if It Equals "Delete"

OilEconomist

Active Member
Joined
Dec 26, 2016
Messages
421
Office Version
  1. 2019
Platform
  1. Windows
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.

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()
 
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.
 
Upvote 0

Excel Facts

Ambidextrous Undo
Undo last command with Ctrl+Z or Alt+Backspace. If you use the Undo icon in the QAT, open the drop-down arrow to undo up to 100 steps.
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).

Code:
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.
 
Upvote 0
OilEconomist,

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

And, should be very fast.
 
Last edited:
Upvote 0
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.
 
Last edited:
Upvote 0
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).

Code:
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.
 
Upvote 0
@footoo....obviously your code was too efficient and sent 'em all into shock !!!
Code:
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
 
Upvote 0
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
 
Upvote 0
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.
 
Upvote 0
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.
 
Last edited:
Upvote 0
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?
 
Upvote 0

Forum statistics

Threads
1,213,487
Messages
6,113,937
Members
448,534
Latest member
benefuexx

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Back
Top