Clear filters on file save

[dwood71]

I have an Excel file used by a number of people and this file has mutliple filtered columns.

Some people never clear the filters before they save the file, and then the next person in doesn't realize the filter is on and enters data in the wrong place.

How can I set the file to clear all filters on file save?

Thanks.

 

[Yard]

Hi and welcome to Mr Excel,

I think you'll need some VBA to achieve this.

Is it just one sheet which is filtered? Does the filtered sheet(s) have Excel Tables, or just an ordinary worksheet Autofilter?

 

[schielrn]

Welcome to the Board!

You could try this:

Private Sub Workbook_BeforeClose(Cancel As Boolean)
Dim r As Range
Set r = ActiveSheet.AutoFilter.Range
r.AutoFilter
r.AutoFilter
End Sub

or maybe even:

Private Sub Workbook_BeforeClose(Cancel As Boolean)
ActiveSheet.ShowAllData
End Sub

Not sure if the last one works before Excel 2007 or not?

Note that this only works with the active sheet while closing. So if a filter is not set you might have a problem and need to put in some checks from there to test if there is an autofilter set up on the sheet or not.

Hope that helps.

 

[dwood71]

Yard,

It's ordinary worksheet autofiliters.