Forum Discussion
Removing data filters automatically on open or close
Hi, Does anyone have a method or know of a setting that will remove or clear data filters when you close or open a spreadsheet? We keep having files closed with filters applied only for the next per...
Press Alt+F11 to activate the Visual Basic Editor.
Double-click ThisWorkbook in the Project Explorer pane on the left, under Microsoft Excel Objects.
Copy the following code into the module:
Private Sub Workbook_Open()
Dim wsh As Worksheet
For Each wsh In Me.Worksheets
If wsh.FilterMode Then
wsh.ShowAllData
End If
Next wsh
End SubSwitch back to Excel.
Save the workbook as a macro-enabled workbook (.xlsm).
Instruct users to allow macros when they open the workbook.
Warning: the code will fail if there is a sheet that has been filtered and protected.
HansVogelaar Brilliant, I'll give it a go thanks
- No worries found it, added the Developer menu
- Alt+F11 didn't do anything, can you tell me the menu route?
