Jul 28 2021 05:23 AM
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 person to open it ending up adding info in the wrong row.......
Jul 28 2021 05:53 AM
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 Sub
Switch 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.
Jul 28 2021 08:51 AM
@Hans Vogelaar Brilliant, I'll give it a go thanks
Jul 28 2021 08:54 AM
Jul 28 2021 08:56 AM