Forum Discussion

BroadswordsBeatie's avatar
BroadswordsBeatie
Copper Contributor
Jul 28, 2021

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 person to open it ending up adding info in the wrong row.......

 

excel

4 Replies

Sort By
  • HansVogelaar's avatar
    HansVogelaar
    MVP
    Jul 28, 2021

    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.

Resources