Reset Workbook VBA:  Show all Data on Filters and clear contents from some sheet ranges
Page 1 of 2 12 LastLast
Results 1 to 10 of 12

Thread: Reset Workbook VBA: Show all Data on Filters and clear contents from some sheet ranges
Thanks Thanks: 0 Likes Likes: 0

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

    Default Reset Workbook VBA: Show all Data on Filters and clear contents from some sheet ranges

    HI, I have tried both below options to either show all data or remove filters in preparation for the workbook to be reused but neither work.

    On top of this I would like to clear contents in range A:W down to last entry on one sheet and a different range on another sheet.
    Both are in tables but I want to leave the formulas in my helper columns so can't clear the contents of whole tables.

    Code:
    Option Explicit
    Sub RemoveAllFilters()
    
    
    Dim Sht As Worksheet
    
    
    For Each Sht In Worksheets
        If Sht.AutoFilterMode = True Then
            Debug.Print Sht.Name
            Sht.AutoFilterMode = False
        End If
    Next
    
    
    
    
    End Sub
    Code:
    Sub ClearFiltersAllSheets()
    
    
    WS_Count = ActiveWorkbook.Worksheets.Count
    
    
    ' Begin the loop to disable all filters
    For i = 1 To WS_Count
        If ThisWorkbook.Sheets(i).FilterMode Then
            ThisWorkbook.Sheets(i).ShowAllData
        End If
    Next i
    
    
    End Sub
    I would like to have a message pop up to say 'reset completed, move to step 2'
    or something like this.

    Many thanks in advance for any advice!

  2. #2
    Board Regular
    Join Date
    Oct 2007
    Posts
    5,807
    Post Thanks / Like
    Mentioned
    8 Post(s)
    Tagged
    2 Thread(s)

    Default Re: Reset Workbook VBA: Show all Data on Filters and clear contents from some sheet ranges

    Try:
    Code:
        For Each Sht In Worksheets
            Sht.AutoFilter.ShowAllData
        Next

  3. #3
    Board Regular
    Join Date
    Oct 2011
    Posts
    352
    Post Thanks / Like
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    Default Re: Reset Workbook VBA: Show all Data on Filters and clear contents from some sheet ranges

    Quote Originally Posted by John_w View Post
    Try:
    Code:
        For Each Sht In Worksheets
            Sht.AutoFilter.ShowAllData
        Next
    Hi John

    Thank you for this however I get an error -perhaps because not every worksheet has an autofilter or data is already showing all for some? Could this be the reason?

    Code:
    Option Explicit
    Sub RemoveAllFilters()
    
    
    Dim Sht As Worksheet
    
    For Each Sht In Worksheets
            Sht.AutoFilter.ShowAllData
        Next
    
    
    
    'For Each Sht In Worksheets
       'If Sht.AutoFilterMode = True Then
          '  Debug.Print Sht.Name
          '  Sht.AutoFilterMode = False
       ' End If
    'Next
    
    
    
    
    
    
    End Sub

  4. #4
    MrExcel MVP
    Moderator
    Fluff's Avatar
    Join Date
    Jun 2014
    Location
    Chippenham
    Posts
    27,029
    Post Thanks / Like
    Mentioned
    460 Post(s)
    Tagged
    46 Thread(s)

    Default Re: Reset Workbook VBA: Show all Data on Filters and clear contents from some sheet ranges

    How about
    Code:
    Sub Melimob()
       Dim Ws As Worksheet
       For Each Ws In Worksheets
          If Ws.AutoFilterMode Or Ws.FilterMode Then Ws.ShowAllData
       Next Ws
    End Sub
    - Posting Data try one of these tools
    - Posting guidelines, forum rules and terms of use
    - Read the FAQs

    Running Office 365 on Win 10

  5. #5
    Board Regular
    Join Date
    Oct 2011
    Posts
    352
    Post Thanks / Like
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    Default Re: Reset Workbook VBA: Show all Data on Filters and clear contents from some sheet ranges

    Quote Originally Posted by Fluff View Post
    How about
    Code:
    Sub Melimob()
       Dim Ws As Worksheet
       For Each Ws In Worksheets
          If Ws.AutoFilterMode Or Ws.FilterMode Then Ws.ShowAllData
       Next Ws
    End Sub
    Hi - thank you for this but for some reason, it's not unfiltering the sheets which have a filter on?

    do I have to say if true and false of something?

    thank you again

  6. #6
    Board Regular
    Join Date
    Oct 2011
    Posts
    352
    Post Thanks / Like
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    Default Re: Reset Workbook VBA: Show all Data on Filters and clear contents from some sheet ranges

    Ok - I think it's because my filters are on tables.

    I've tried to use your code and another I found as I need it to loop through worksheets and tables..

    this doesn't work tho.. any ideas?

    Code:
    Sub ClearFiltersAllSheets()
    
    
    Dim Ws As Worksheet
    Dim lo As ListObject
       For Each Ws In Worksheets
           For Each lo In Ws.ListObjects
      
        'Clear All Filters for entire Table
        lo.AutoFilter.ShowAllData
        '  If Ws.AutoFilterMode Or Ws.FilterMode Then Ws.ShowAllData
       Next Ws
        
      Next lo
      'Loop through all Tables on the sheet
      
        
          
    End Sub
    thank you in advance!

  7. #7
    Board Regular
    Join Date
    Oct 2011
    Posts
    352
    Post Thanks / Like
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    Default Re: Reset Workbook VBA: Show all Data on Filters and clear contents from some sheet ranges

    Quote Originally Posted by Melimob View Post
    Ok - I think it's because my filters are on tables.

    I've tried to use your code and another I found as I need it to loop through worksheets and tables..

    this doesn't work tho.. any ideas?

    Code:
    Sub ClearFiltersAllSheets()
    
    
    Dim Ws As Worksheet
    Dim lo As ListObject
       For Each Ws In Worksheets
           For Each lo In Ws.ListObjects
      
        'Clear All Filters for entire Table
        lo.AutoFilter.ShowAllData
        '  If Ws.AutoFilterMode Or Ws.FilterMode Then Ws.ShowAllData
       Next Ws
        
      Next lo
      'Loop through all Tables on the sheet
      
        
          
    End Sub
    thank you in advance!
    Ok found this which is exactly what I need but it's giving me an error on: listObj.AutoFilter.ShowAllData

    Code:
    Sub ResetFilters()
          Dim ws As Worksheet
          Dim wb As Workbook
          Dim listObj As ListObject
           Set wb = ThisWorkbook
           'Set wb = ActiveWorkbook
           'This is if you place the macro in your personal wb to be able to reset the filters on any wb you're currently working on. Remove the set wb = thisworkbook if that's what you need
               For Each ws In wb.Worksheets
                  If ws.FilterMode Then
                  ws.ShowAllData
                  Else
                  End If
         'This removes "normal" filters in the workbook - however, it doesn't remove table filters
           For Each listObj In ws.ListObjects
                If listObj.ShowHeaders Then
                     listObj.AutoFilter.ShowAllData
                     listObj.Sort.SortFields.Clear
                End If
           Next listObj
    
    
                Next
    'And this removes table filters. You need both aspects to make it work.
        End Sub
    any ideas welcome please! thank you

  8. #8
    MrExcel MVP
    Moderator
    Fluff's Avatar
    Join Date
    Jun 2014
    Location
    Chippenham
    Posts
    27,029
    Post Thanks / Like
    Mentioned
    460 Post(s)
    Tagged
    46 Thread(s)

    Default Re: Reset Workbook VBA: Show all Data on Filters and clear contents from some sheet ranges

    You've got the two Next lines the wrong way round.
    - Posting Data try one of these tools
    - Posting guidelines, forum rules and terms of use
    - Read the FAQs

    Running Office 365 on Win 10

  9. #9
    Board Regular
    Join Date
    Oct 2011
    Posts
    352
    Post Thanks / Like
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    Default Re: Reset Workbook VBA: Show all Data on Filters and clear contents from some sheet ranges

    thanks Fluff but neither still work when the table is already showing all data?

  10. #10
    MrExcel MVP
    Moderator
    Fluff's Avatar
    Join Date
    Jun 2014
    Location
    Chippenham
    Posts
    27,029
    Post Thanks / Like
    Mentioned
    460 Post(s)
    Tagged
    46 Thread(s)

    Default Re: Reset Workbook VBA: Show all Data on Filters and clear contents from some sheet ranges

    Try
    Code:
          For Each listobj In Ws.ListObjects
             listobj.AutoFilter.ShowAllData
             listobj.Sort.SortFields.Clear
           Next listobj
    - Posting Data try one of these tools
    - Posting guidelines, forum rules and terms of use
    - Read the FAQs

    Running Office 365 on Win 10

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
  •