VBA Filter by selection & Delete on all sheets

[thedeadzeds]

Hi Guys,

Is there a way to do the following:

Select all sheets except one called 'Data'
Apply the auto filter and filter on column L
Delete all the data with the exception of 'Cambelt timing'

I know i can do this via a macro but sometimes some of the worksheets does not have Cambelt timing in column L so it brings up an error.

Many thanks

 

[thedeadzeds]

Ok, so ive found some code and adapted it which works great but i need this to work on all sheers with the exception of the sheet called 'Data' and 'Site'. Can anyone advise please?

Option ExplicitSub FilterData()
    
'Below is the complete program to delte only the visible filtered rows from a selected range.
    
    
    
    Sheets("Bristol Audi").Select
    If ActiveSheet.AutoFilterMode Then Selection.AutoFilter
    
    ActiveCell.CurrentRegion.Select
    
    With Selection
        .AutoFilter
        .AutoFilter Field:=12, Criteria1:="<>Cam Belt timing replacement"
        .Offset(1, 0).Select
    End With
    
    Dim numRows As Long, numColumns As Long
    numRows = Selection.Rows.Count
    numColumns = Selection.Columns.Count
    
    Selection.Resize(numRows - 1, numColumns).Select
    
    With Selection
  
    .SpecialCells(xlCellTypeVisible).EntireRow.Delete
    End With
    
    ActiveSheet.ShowAllData






End Sub

 

[Fluff]

Try this

Sub FilterData()
'Below is the complete program to delte only the visible filtered rows from a selected range.
    
    Dim Ws As Worksheet

    For Each Ws In Worksheets
        If Not Ws.Name = "Data" And Not Ws.Name = "Site" Then
            With Ws.Range("A1").CurrentRegion
                If Ws.AutoFilterMode Then .AutoFilter
                .AutoFilter
                .AutoFilter Field:=12, Criteria1:="<>ABC"
                On Error Resume Next
                .Offset(1, 0).SpecialCells(xlCellTypeVisible).Select
                On Error GoTo 0
            End With
        End If
        Ws.ShowAllData
    Next Ws

End Sub

This assumes that your data starts in A1, on each sheet. If that is not the case let me know.