Removing data filters automatically on open or close

Copper Contributor

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.......

 

4 Replies

@BroadswordsBeatie 

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.

@Hans Vogelaar Brilliant, I'll give it a go thanks

Alt+F11 didn't do anything, can you tell me the menu route?
No worries found it, added the Developer menu