macro to Clear all Filters in certain sheets

[hajiali]

I have the following code to Clear Data form all Sheets named "CSA"

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

 

[sadboy309]

Clear Filter:

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

 

[Michael M]

Maybe this

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]

Thanks All for the Reply. appreciate Michael M and Sadboy309

 

[hajiali]

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

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]

How about

With sh
   If .FilterMode Then .ShowAllData
   .Range("A3:A122,a124:a153").ClearContents
End With

 

[hajiali]

Much Appreciate it Fluff that worked

 

[Fluff]

You're welcome & thanks for the feedback