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"

 

[Trebor76]

Hi YasserKhalil,

Though there's endless posts on this (I know I've answered similar posts to this a number of times) if you could answer the following I should be able to put something together;

• What column needs to be checked,
• What is the starting row number, and
• How many rows (roughly) need to be checked

Regards,

Robert

 

[YasserKhalil]

thanks for quick reply
I have a range("A1:A100") and want to delete the entire row if it hasn't the text "man"

 

[Trebor76]

Is it only "man" or "I'm a man"?

 

[YasserKhalil]

just "man"

 

[Trebor76]

As this will potentially delete rows, try it initially on a copy of your data in case the results are not as expected:

'http://www.mrexcel.com/forum/showthread.php?t=572619
Sub Macro1()

    'Delete any row(s) in A1:A100 that contain the text 'man'.

    With [A1:A100]
        .Replace "man", "#N/A", xlWhole
        On Error Resume Next 'Account for no matches
            .SpecialCells(xlCellTypeConstants, xlErrors).EntireRow.Delete
        On Error GoTo 0
    End With

End Sub

Regards,

Robert

 

[YasserKhalil]

It doesn't work
I want to delete the entire empty rows and the rows that doesn't contain the text "man"
I hope it's clear now

 

[Trebor76]

I dare say there's a more efficient way (like my original post but where non matching text is replaced with "#N/A"), but with such a small data set try this (again, initially on a sample of the data):

Option Explicit
Sub Macro1()

    'http://www.mrexcel.com/forum/showthread.php?t=572619
    
    'Delete any row(s) in A1:A100 that does not contain the text 'man'.

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

    For Each rngCell In [A1:A100]
        If Len(rngCell) > 0 And rngCell.Value <> "man" 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

 

[Jaafar Tribak]

Hi Yasser ! long time no see

Yet another routine but without looping ( Please try this in a copy of your sheet in case it deletes the wrong data ! )

Sub DeleteRows( _
ByVal Target As Range, _
ByVal Field As Long, _
ByVal ExclusionCriteria As String _
)

    Dim oVisibleCells As Range
    Dim lCalcMode As Long

    With Application
        lCalcMode = .Calculation
        .ScreenUpdating = False
        .EnableEvents = False
        With Target
            .AutoFilter Field:=Field, Criteria1:=ExclusionCriteria
            Set oVisibleCells = .SpecialCells(xlCellTypeVisible)
            .Parent.AutoFilterMode = False
            .AutoFilter
            .ClearContents
            oVisibleCells.SpecialCells(xlCellTypeVisible).Value = ExclusionCriteria
            .SpecialCells(xlCellTypeBlanks).EntireRow.Delete
        End With
        .ScreenUpdating = True
        .EnableEvents = True
        .Calculation = lCalcMode
    End With
    
End Sub

Usage example :

Sub Test()

    DeleteRows Range("$A$1:$A$100"), 1, "man"

End Sub

 

[YasserKhalil]

Mr. Jaafar Tribak
I missed you so much

Thanks for both of you
But the both codes doesn't fulfill my request

I want to keep just the rows that contains the text "man"
and the other rows have to be deleted