Deleting rows containing specific text

[chadski778]

Could i please have code for deleting a row containing specific text, for example, 'apple', in a spreadsheet.
Thanks

 

[Sektor]

Something like this:

[COLOR="Blue"]Sub[/COLOR] DeletingRows()

    [COLOR="Blue"]Dim[/COLOR] i [COLOR="Blue"]As[/COLOR] [COLOR="Blue"]Long[/COLOR]
    
    Application.ScreenUpdating = [COLOR="Blue"]False[/COLOR]
    
    [COLOR="Blue"]For[/COLOR] i = Cells(Rows.Count, 1).End(xlUp).Row [COLOR="Blue"]To[/COLOR] 1 [COLOR="Blue"]Step[/COLOR] -1
        Cells(i, 1).EntireRow.Delete
    [COLOR="Blue"]Next[/COLOR]

[COLOR="Blue"]End[/COLOR] [COLOR="Blue"]Sub[/COLOR]

 

[Peter_SSs]

Assuming you have a heading row, you could try something like this. I've assumed the text of interest is in column B - adjust to suit your layout.

I suggest that you test in a copy of your workbook.

<font face=Courier New><br><SPAN style="color:#00007F">Sub</SPAN> DeleteRows()<br>    Application.ScreenUpdating = <SPAN style="color:#00007F">False</SPAN><br>    <SPAN style="color:#00007F">With</SPAN> Range("B1", Range("B" & Rows.Count).End(xlUp))<br>        .AutoFilter Field:=1, Criteria1:="=Apple"<br>        <SPAN style="color:#00007F">On</SPAN> <SPAN style="color:#00007F">Error</SPAN> <SPAN style="color:#00007F">Resume</SPAN> <SPAN style="color:#00007F">Next</SPAN><br>        .Offset(1).Resize(.Rows.Count - 1) _<br>            .SpecialCells(xlCellTypeVisible).EntireRow.Delete<br>        <SPAN style="color:#00007F">On</SPAN> <SPAN style="color:#00007F">Error</SPAN> <SPAN style="color:#00007F">GoTo</SPAN> 0<br>        .AutoFilter<br>    <SPAN style="color:#00007F">End</SPAN> <SPAN style="color:#00007F">With</SPAN><br>    Application.ScreenUpdating = <SPAN style="color:#00007F">True</SPAN><br><SPAN style="color:#00007F">End</SPAN> <SPAN style="color:#00007F">Sub</SPAN><br></FONT>

 

[mirabeau]

If your "apple" is anywhere in the rows of the worksheet, not necessarily in a prespecified column, and also if "apple" may be part of a cell rather than necessarily being the whole cell, then you could try this

Sub delro()
Dim i As Long, fnd
With ActiveSheet.UsedRange
    For i = .Rows.Count To 1 Step -1
        Set fnd = .Rows(i).Find("apple", lookat:=xlPart)
        If Not fnd Is Nothing Then .Rows(i).Delete xlUp
    Next i
End With
End Sub