macro to Clear all Filters in certain sheets

hajiali

Well-known Member
Joined
Sep 8, 2018
Messages
623
Office Version
  1. 2016
Platform
  1. Windows
I have the following code to Clear Data form all Sheets named "CSA"

Code:
Sub Clear_All_Values()    Dim ws As Worksheet, sh As Worksheet
    Set ws = ActiveSheet
    If MsgBox("ARE YOU SURE YOU WANT TO CLEAR ALL AGENTS RESULTS?", vbYesNo, "CONFRIM") = vbYes Then
        Worksheets("CSA1").Select
        For Each sh In Worksheets
            If Left(sh.Name, 3) = "CSA" Then
               sh.Select Replace:=False
            End If
        Next
        Worksheets("CSA1").Activate
        Range("A3:A122,a124:a153").Select
        Selection.ClearContents
        Range("A1").Select
        ws.Select
    End If
End Sub

This code works when the sheets when filters are not active. what I want to add to this code is to Clear All Filters in columns A - Column P before it runs the code to .Clearcontents
 

Excel Facts

Create a chart in one keystroke
Select the data and press Alt+F1 to insert a default chart. You can change the default chart to any chart type
Clear Filter:

Code:
If ActiveSheet.AutoFilterMode Then
ActiveSheet.ShowAllData 
'or
ActiveSheet.AutoFilterMode = False
End If

'or

Dim lo As ListObject
  'Loop through all Tables on the sheet
  For Each lo In Sheet1.ListObjects
  
    'Clear All Filters for entire Table
    lo.AutoFilter.ShowAllData
    'Or
    lo.ShowAutoFilter = False

    
Next lo

'or

Cells.AutoFilter
 
Last edited:
Upvote 0
Maybe this
Code:
Sub MM1()
Dim sh As Worksheet
If MsgBox("ARE YOU SURE YOU WANT TO CLEAR ALL AGENTS RESULTS?", vbYesNo, "CONFRIM") = vbYes Then
    For Each sh In Worksheets
        If Left(sh.Name, 3) = "CSA" Then
             With sh
                .Cells.AutoFilter
                .Range("A3:A122,a124:a153").ClearContents
            End With
        End If
    Next sh
End If
End Sub
 
Upvote 0
Thanks All for the Reply. appreciate Michael M and Sadboy309
 
Upvote 0
Hello Michael M thanks for the code below. I found out the .Cells.AutoFilter portion of the code Remove the Filter option which is not exactly what I was looking for. I want to keep the filter on however I wanted it to reset all filters other words to simulate as if i would to Select All on the Filter drop down before the .clearcontents. I hope this makes more clear.

Maybe this
Code:
Sub MM1()
Dim sh As Worksheet
If MsgBox("ARE YOU SURE YOU WANT TO CLEAR ALL AGENTS RESULTS?", vbYesNo, "CONFRIM") = vbYes Then
    For Each sh In Worksheets
        If Left(sh.Name, 3) = "CSA" Then
             With sh
                .Cells.AutoFilter
                .Range("A3:A122,a124:a153").ClearContents
            End With
        End If
    Next sh
End If
End Sub
 
Upvote 0
How about
Code:
With sh
   If .FilterMode Then .ShowAllData
   .Range("A3:A122,a124:a153").ClearContents
End With
 
Upvote 0
You're welcome & thanks for the feedback
 
Upvote 0

Forum statistics

Threads
1,213,561
Messages
6,114,317
Members
448,564
Latest member
ED38

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
Back
Top