Results 1 to 8 of 8

Thread: macro to Clear all Filters in certain sheets

  1. #1
    Board Regular
    Join Date
    Sep 2018
    Posts
    58
    Post Thanks / Like
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    Default macro to Clear all Filters in certain sheets

    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

  2. #2
    Board Regular
    Join Date
    Oct 2014
    Posts
    88
    Post Thanks / Like
    Mentioned
    1 Post(s)
    Tagged
    0 Thread(s)

    Default Re: macro to Clear all Filters in certain sheets

    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 by sadboy309; Apr 28th, 2019 at 09:17 PM.

  3. #3
    Board Regular Michael M's Avatar
    Join Date
    Oct 2005
    Location
    South Western NSW
    Posts
    17,325
    Post Thanks / Like
    Mentioned
    17 Post(s)
    Tagged
    2 Thread(s)

    Default Re: macro to Clear all Filters in certain sheets

    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
    Regards
    Michael M
    ---------------------------------------
    The more I learn, the less I seem to know.....A Please and Thank You cost nothing !
    It's easier to debug if we can see the whole macro !
    Home 2007 & 2013

    - Posting guidelines, forum rules and terms of use

    - To download Mr Excel HTML Maker

    - Try searching for your answer first, see how

    - Read the FAQs

    - List of BB codes


    [CODE]Place Your Code Here[/CODE]

  4. #4
    Board Regular
    Join Date
    Sep 2018
    Posts
    58
    Post Thanks / Like
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    Default Re: macro to Clear all Filters in certain sheets

    Thanks All for the Reply. appreciate Michael M and Sadboy309

  5. #5
    Board Regular
    Join Date
    Sep 2018
    Posts
    58
    Post Thanks / Like
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    Default Re: macro to Clear all Filters in certain sheets

    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.

    Quote Originally Posted by Michael M View Post
    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

  6. #6
    MrExcel MVP
    Moderator
    Fluff's Avatar
    Join Date
    Jun 2014
    Location
    Chippenham
    Posts
    23,093
    Post Thanks / Like
    Mentioned
    390 Post(s)
    Tagged
    41 Thread(s)

    Default Re: macro to Clear all Filters in certain sheets

    How about
    Code:
    With sh
       If .FilterMode Then .ShowAllData
       .Range("A3:A122,a124:a153").ClearContents
    End With
    - Posting Data try one of these tools
    - Posting guidelines, forum rules and terms of use
    - Read the FAQs

    Running Office 2003 & 2013 on Win 7

  7. #7
    Board Regular
    Join Date
    Sep 2018
    Posts
    58
    Post Thanks / Like
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    Default Re: macro to Clear all Filters in certain sheets

    Much Appreciate it Fluff that worked

  8. #8
    MrExcel MVP
    Moderator
    Fluff's Avatar
    Join Date
    Jun 2014
    Location
    Chippenham
    Posts
    23,093
    Post Thanks / Like
    Mentioned
    390 Post(s)
    Tagged
    41 Thread(s)

    Default Re: macro to Clear all Filters in certain sheets

    You're welcome & thanks for the feedback
    - Posting Data try one of these tools
    - Posting guidelines, forum rules and terms of use
    - Read the FAQs

    Running Office 2003 & 2013 on Win 7

Some videos you may like

User Tag List

Tags for this Thread

Like this thread? Share it with others

Like this thread? Share it with others

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •