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

[Melimob]

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.

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

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!

 

[John_w]

Try:

    For Each Sht In Worksheets
        Sht.AutoFilter.ShowAllData
    Next

 

[Melimob]

Try:

    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?

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

 

[Fluff]

How about

Sub Melimob()
   Dim Ws As Worksheet
   For Each Ws In Worksheets
      If Ws.AutoFilterMode Or Ws.FilterMode Then Ws.ShowAllData
   Next Ws
End Sub

 

[Melimob]

How about

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

 

[Melimob]

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?

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!

 

[Melimob]

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?

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

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

 

[Fluff]

You've got the two Next lines the wrong way round.

 

[Melimob]

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

 

[Fluff]

Try

      For Each listobj In Ws.ListObjects
         listobj.AutoFilter.ShowAllData
         listobj.Sort.SortFields.Clear
       Next listobj