如何在打开文件时自动删除应用的筛选器而不删除筛选器功能

Question

我有一个在共享电子表格上远程工作的团队。他们可以应用筛选器进行搜索。我希望电子表格能够在关闭或打开电子表格时自动清除以前应用的过滤器，而不会删除设置未来过滤器的能力。我找不出能让它工作的代码。

我已经搜索了这些线程，并尝试了许多代码。有些已经接近并在打开电子表格时删除了筛选器，但它也删除了筛选功能。这意味着我必须在每次重新打开电子表格时打开过滤功能，这并不理想。使用的代码为：

Private Sub Workbook_Open()
    Dim ws As Worksheet
        For Each ws In Worksheets
            If ws.AutoFilterMode Then
                ws.AutoFilterMode = False
            End If
        Next ws
End Sub

Answer 1

这将清除，但保留过滤器：

Sub Workbook_Open()
    Dim ws As Worksheet
    For Each ws In Worksheets
        If ws.AutoFilterMode Then 
            ws.AutoFilter.ShowAllData
        End If 
    Next ws 
End Sub

Answer 2

另一种方法可以是：

Sub clearFilter()
Dim sht As Worksheet 'Declare a worksheet variable
Dim rng As Range 'Declare a Range variable
Dim j As Long
Set sht = ThisWorkbook.Worksheets("Name of your Worksheet") 'The worksheet where the data is
Set rng = sht.Range("A:E") 'The range that is being filtered. In this case columns A,B,C,D,E are being filtered.

For j = 1 To rng.Columns.Count Step 1 'loop through all the columns that are being filtered...
    rng.AutoFilter Field:=j '...and clear the filter while maintaining the filtering capabilities
Next j

End Sub