FILTER OR DELETE ROWS CONTAINING WORDS STARTING AND ENDING WITH SPECIFIC TEXT USING VBA

[turkanet]

Dear Freinds,
I need to filter or delete rows in A:N which contain text in N:N starts with "DOV" and ends with "Z" (like DOVTINZ, DOVZ, DOVEZ, etc). If cell is "MARY DOVZ EAGLE", row will be deleted.
Thank you

 

[kevin9999]

Please try the following on a copy of your workbook. Assumes your headers are are on row 1 and that your data starts in row 2. Change the sheet name to suit. This macro deletes the rows.

Option Explicit
Sub DovZ()
    Dim ws As Worksheet
    Set ws = Worksheets("Sheet1")   '<-- *** Change name of sheet to suit ****
    If ws.AutoFilterMode Then ws.AutoFilter.ShowAllData
    With ws.Range("A1").CurrentRegion
        .AutoFilter 14, "*DOV*Z*"
        If .SpecialCells(xlCellTypeVisible).Address <> .Rows(1).Address Then
            .Offset(1).EntireRow.Delete
        End If
        .AutoFilter
    End With
End Sub

 

[mse330]

This is another option ...

Sub test()

With Range("N1", Range("N" & Rows.Count).End(3))
    .Value = Evaluate(Replace("iferror(if(search(""DOV*Z"",@)>0,""#N/A""),@)", "@", .Address))
    On Error Resume Next
        .SpecialCells(2, xlErrors).EntireRow.Delete
    On Error GoTo 0
End With

End Sub