macro to Clear all Filters in certain sheets

hajiali

Board Regular
Joined
Sep 8, 2018
Messages
192
Office Version
2016
Platform
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
 

Some videos you may like

Excel Facts

Excel Can Read to You
Customize Quick Access Toolbar. From All Commands, add Speak Cells or Speak Cells on Enter to QAT. Select cells. Press Speak Cells.

sadboy309

Board Regular
Joined
Oct 15, 2014
Messages
88
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:

Michael M

Well-known Member
Joined
Oct 27, 2005
Messages
18,799
Office Version
2013
Platform
Windows
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
 

hajiali

Board Regular
Joined
Sep 8, 2018
Messages
192
Office Version
2016
Platform
Windows
Thanks All for the Reply. appreciate Michael M and Sadboy309
 

hajiali

Board Regular
Joined
Sep 8, 2018
Messages
192
Office Version
2016
Platform
Windows
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
 

Fluff

MrExcel MVP, Moderator
Joined
Jun 12, 2014
Messages
39,074
Office Version
365
Platform
Windows
How about
Code:
With sh
   If .FilterMode Then .ShowAllData
   .Range("A3:A122,a124:a153").ClearContents
End With
 

hajiali

Board Regular
Joined
Sep 8, 2018
Messages
192
Office Version
2016
Platform
Windows
Much Appreciate it Fluff that worked
 

Fluff

MrExcel MVP, Moderator
Joined
Jun 12, 2014
Messages
39,074
Office Version
365
Platform
Windows
You're welcome & thanks for the feedback
 

Watch MrExcel Video

Forum statistics

Threads
1,096,324
Messages
5,449,729
Members
405,575
Latest member
Masimo85

This Week's Hot Topics

Top