Delete entire row if it contains a specific word

[Computerised10]

hi all,

how am i able to delete a entire row if it contains the word p6

thanks

 

[Momentman]

If you need to use a macro, maybe like this

Sub deleterows()
    Dim I As Integer
    For I = Range("A" & Rows.Count).End(xlUp).Row To 1 Step -1
        If WorksheetFunction.CountIf(Rows(I).EntireRow, "p6") > 0 Then
            Rows(I).EntireRow.Delete
        End If
    Next I
End Sub

 

[NewOrderFac33]

Or perhaps:

Sub DeleteRowsContainingP6()
        
    Dim MyRange As Range
    Dim MyTotal As Long
    
    Set MyRange = Range("A1:C200")
    MyTotal = MyRange.Cells.Count
    
    For MyCount = MyTotal To 1 Step -1
        If InStr(1, MyRange.Cells(MyCount).Formula, "P6") <> 0 Then
        MyRange.Cells(MyCount).EntireRow.Delete
        End If
    Next
    
End Sub

This will work for cells that CONTAIN "P6" as well as equalling it - just alter the MyRange reference to suit your needs.

Hope this helps

Pete

 

[Computerised10]

Or perhaps:

Sub DeleteRowsContainingP6()
        
    Dim MyRange As Range
    Dim MyTotal As Long
    
    Set MyRange = Range("A1:C200")
    MyTotal = MyRange.Cells.Count
    
    For MyCount = MyTotal To 1 Step -1
        If InStr(1, MyRange.Cells(MyCount).Formula, "P6") <> 0 Then
        MyRange.Cells(MyCount).EntireRow.Delete
        End If
    Next
    
End Sub

thanks man worked a treat.

 

[Momentman]

Or perhaps:


This will work for cells that CONTAIN "P6" as well as equalling it - just alter the MyRange reference to suit your needs.

Hope this helps

Pete

Countif with wildcards would work as well as INSTR

 

[NewOrderFac33]

Thanks for that, Momentman. I'm still having fun with your MouseUp/MouseDown solution from the other week!

Pete

 

[NewOrderFac33]

You're welcome!

Pete