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()

 

[Rick Rothstein]

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 [B][COLOR="#FF0000"]LCase[/COLOR]([/B]Cells(i, 2).Value[B][COLOR="#FF0000"])[/COLOR][/B] = "delete" Then Cells(i, 2).EntireRow.Delete
        
        Next i

End Sub()

Unlike Excel worksheet functions, VBA comparisons are case sensitive. Try adding what I show in red above and see if that makes your code work.

 

[OilEconomist]

Thanks Rick for your help.

I'm still getting a run time error.

 

[Rick Rothstein]

I'm still getting a run time error.

On what line of code?

 

[footoo]

Alternative :

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

 

[hiker95]

OilEconomist,

It is always easier to help and test possible solutions if we could work with your actual file.

Perhaps you could upload a copy of your file to a free site such as www.box.com or www.dropbox.com

Once you do that, mark it for 'Sharing' and you will be given a link to the file that you can post here.

Include a detailed explanation of what you would like to do referring to specific cells and worksheets.

If the workbook contains confidential information, you could replace it with generic data.

 

[MARK858]

I'm still getting a run time error.

What does the error message actually state? the obvious errors are that i is larger than an Integer or if there is protection or merged cells preventing the delete, but it is not easy to guess what the issue is without knowing what the error actually states.

 

[hiker95]

OilEconomist,

You want to change the next line of code:

Dim i As Integer

To

Dim i As Long

 

[OilEconomist]

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.

 

[footoo]

Did you try the code per post # 5 ?