VBA Filter by selection & Delete on all sheets

thedeadzeds

Active Member
Joined
Aug 16, 2011
Messages
317
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
 
Last edited:

Some videos you may like

Excel Facts

Back into an answer in Excel
Use Data, What-If Analysis, Goal Seek to find the correct input cell value to reach a desired result

thedeadzeds

Active Member
Joined
Aug 16, 2011
Messages
317
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?

Code:
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

MrExcel MVP, Moderator
Joined
Jun 12, 2014
Messages
53,203
Office Version
  1. 365
Platform
  1. Windows
Try this
Code:
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.
 

Watch MrExcel Video

Forum statistics

Threads
1,123,135
Messages
5,599,917
Members
414,348
Latest member
KloppyM

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Top