Delete entire row with a condition

[YasserKhalil]

Hi everybody
I want a code that could delete entire row if the cell value doesn't contain specific text say "man"

 

[hippiehacker]

Option Explicit
Sub test()
ActiveSheet.Range("A1", "A1").End(xlDown).AutoFilter Field:=1, Criteria1:="man"
Application.DisplayAlerts = False
ActiveSheet.UsedRange.Offset(1, 0).SpecialCells(xlCellTypeVisible).Delete
Application.DisplayAlerts = True
ActiveSheet.AutoFilterMode = False
End Sub

 

[YasserKhalil]

Yes, taurean
That's perfect
That's what I want

Thank you very much for your great help

 

[hippiehacker]

Option Explicit
Sub test()
ActiveSheet.Range("A1", "A1").End(xlDown).AutoFilter Field:=1, Criteria1:="man"
Application.DisplayAlerts = False
ActiveSheet.UsedRange.Offset(1, 0).SpecialCells(xlCellTypeVisible).Delete
Application.DisplayAlerts = True
ActiveSheet.AutoFilterMode = False
End Sub

Sorry change to

Option Explicit
Sub test()
ActiveSheet.Range("A1", "A1").End(xlDown).AutoFilter Field:=1, Criteria1:="<>" & "man"
Application.DisplayAlerts = False
ActiveSheet.UsedRange.Offset(1, 0).SpecialCells(xlCellTypeVisible).Delete
Application.DisplayAlerts = True
ActiveSheet.AutoFilterMode = False
End Sub

 

[Trebor76]

I'm sorry
I didn't understand what he means.......

Then you should have asked me to explain not answer incorrectly which then leads to an incorrect solution

That said, and though taurean has provided a nice solution, here's my tweaked solution:

Option Explicit
Sub Macro1()

    'http://www.mrexcel.com/forum/showthread.php?t=572619
    
    'Delete any row(s) in A1:A[where ever the last row is] _
    that does not contain the text 'man' within the cell.

    Dim rngCell As Range, _
        rngDeleteRange As Range
        
    Application.ScreenUpdating = False

    For Each rngCell In Range("A1:A" & Range("A" & Rows.Count).End(xlUp).Row)
        If InStr(rngCell.Value, "man") = 0 Then
            'Cater for initial setting of 'rngDelRange' range
            If rngDeleteRange Is Nothing Then
                Set rngDeleteRange = Cells(rngCell.Row, rngCell.Column)
            Else
                Set rngDeleteRange = Union(rngDeleteRange, Cells(rngCell.Row, rngCell.Column))
            End If
        End If
    Next rngCell
    
    If Not rngDeleteRange Is Nothing Then
        rngDeleteRange.EntireRow.Delete
    End If
    
    Application.ScreenUpdating = True
    
End Sub

 

[ReubenLee¨¤]

Hugo Boss Fake - IT has been h

thanks! been trying to work that'un out for quite a while..