Filter and delete visible rows

[paldob]

Hi All,

Could anyone provide me with a simple way to do the following

• Sheet1 (my main worksheet)
• Cell A2 is my Filter Title
• Cell A3:CH3 downwards contains the information I wish to filter
• I want to filter column A (Data From A3 to Axxx) to select the word DELETE (I have a routine to mark duplicate information as DELETE) I then want to remove all visible rows that contain the word delete in column A

Is this possible?

Thank you
P

 

[steve the fish]

Try this:

Application.ScreenUpdating = False
 
Dim lr As Long

With Sheets("Sheet1")
    If .AutoFilterMode Then .AutoFilterMode = False
    lr = .Range("A" & .Rows.Count).End(xlUp).Row
    If lr < 4 Then Exit Sub
    With .Range("A3:A" & lr)
        .AutoFilter
        .AutoFilter Field:=1, Criteria1:="DELETE"
        If .SpecialCells(xlCellTypeVisible).Count > 1 Then
            .Offset(1, 0).Resize(lr - 1).SpecialCells(xlCellTypeVisible).EntireRow.Delete
        End If
    End With
    .AutoFilterMode = False
End With

Application.ScreenUpdating = True