Excel 2013 VBA 清除所有过滤器宏

Excel 2013 VBA Clear All Filters macro

提问by CJSoldier

It seems older macros are not working. I have proper securtiy set to run VBA macros but when I have tried a few methods for clearing ALL filters on a worksheet, I get a compile error.

似乎较旧的宏不起作用。我有适当的安全设置来运行 VBA 宏，但是当我尝试了几种清除工作表上所有过滤器的方法时，出现编译错误。

Here is what I have tried:

这是我尝试过的：

Sub AutoFilter_Remove()
'This macro removes any filtering in order to display all of the data but it does not remove the filter arrows
ActiveSheet.ShowAllData
End Sub

I have buttons on the sheets to clear all filters for ease of use for users since the sheets has a lot of columns that have filters on them.

我在工作表上有按钮来清除所有过滤器，以便用户使用，因为工作表上有很多带有过滤器的列。

采纳答案by Gary's Student

If the sheet already has a filter on it then:

如果工作表上已经有过滤器，则：

Sub Macro1()
    Cells.AutoFilter
End Sub

will remove it.

将删除它。

回答by CRondao

Try this:

尝试这个：

If ActiveSheet.AutoFilterMode Then ActiveSheet.ShowAllData

回答by BobbyA

ShowAllData will throw an error if a filter isn't currently applied. This will work:

如果当前未应用过滤器，则 ShowAllData 将引发错误。这将起作用：

Sub ResetFilters()
    On Error Resume Next
    ActiveSheet.ShowAllData
End Sub

回答by Aaron Donnelly

For tables try this to check if it's on and turn off:

对于表，请尝试检查它是否打开并关闭：

If wrkSheetCodeName.ListObjects("TableName").ShowAutoFilter Then
    wrkSheetCodeName.ListObjects("TableName").Range.AutoFilter
End if

To Turn back on:

重新开启：

wrkSheetCodeName.ListObjects("TableName").Range.AutoFilter

回答by saltorr

this works nice.!

这很好用。！

If ActiveSheet.AutoFilterMode Then Cells.AutoFilter

回答by Shadmage

That is brilliant, the only answer I found that met my particular need, thanks SO much for putting it up!

那太棒了，我发现的唯一满足我特殊需求的答案，非常感谢你提出来！

I made just a minor addition to it so that the screen didn't flash and it removes and subsequently reapplies the password on each sheet as it cycles through [I have the same password for all sheets in the workbook]. In the spirit of your submission, I add this to assist anyone else....

我只是对它做了一个小补充，这样屏幕就不会闪烁，它会在每个工作表循环时删除并随后重新应用密码[我对工作簿中的所有工作表都有相同的密码]。本着您提交的精神，我添加了此内容以帮助其他人......

Sub ClearFilters()
    Application.ScreenUpdating = False

    On Error Resume Next
    For Each wrksheet In ActiveWorkbook.Worksheets
        'Change the password to whatever is required
        wrksheet.Unprotect Password:="Albuterol1"
        wrksheet.ShowAllData 'This works for filtered data not in a table

        For Each lstobj In wrksheet.ListObjects
            If lstobj.ShowAutoFilter Then
                lstobj.Range.AutoFilter 'Clear filters from a table
                lstobj.Range.AutoFilter 'Add the filters back to the table
            End If

        'Change the password to whatever is required
        wrksheet.Protect Password:="Albuterol1", _
              DrawingObjects:=True, _
              Contents:=True, _
              Scenarios:=True, _
              AllowFiltering:=True
        Next 'Check next worksheet in the workbook
    Next

    Application.ScreenUpdating = True
End Sub

I know this is a relatively old post and don't really like being a necromancer... But since I had the same issue and tried a few of the options in this thread without success I combined some of the answers to get a working macro..

我知道这是一篇相对较旧的帖子，并不真正喜欢成为死灵法师......但是由于我遇到了同样的问题并且在这个线程中尝试了一些选项但没有成功，我结合了一些答案来获得一个有效的宏..

Hopefully this helps someone out there :)

希望这可以帮助那里的人:)

Sub ResetFilters()
    On Error Resume Next
    For Each wrksheet In ActiveWorkbook.Worksheets
        wrksheet.ShowAllData 'This works for filtered data not in a table
        For Each lstobj In wrksheet.ListObjects
            If lstobj.ShowAutoFilter Then
                lstobj.Range.AutoFilter 'Clear filters from a table
                lstobj.Range.AutoFilter 'Add the filters back to the table
            End If
        Next 'Check next worksheet in the workbook
    Next
End Sub

回答by Julius Getz M?rk

I found this workaround to work pretty effectively. It basically removes autofilter from the table and then re-applies it, thus removing any previous filters. From my experience this is not prone to the error handling required with the other methods mentioned here.

我发现这个解决方法非常有效。它基本上从表中删除自动过滤器，然后重新应用它，从而删除任何以前的过滤器。根据我的经验，这不容易出现此处提到的其他方法所需的错误处理。

Set myTable = YOUR_SHEET.ListObjects("YourTableName")

myTable.ShowAutoFilter = False
myTable.ShowAutoFilter = True

回答by Ejaz Ahmed

There are two types of filters in Excel:

Excel 中有两种类型的过滤器：

• Auto Filter
• Advanced Filter

• 自动过滤器
• 高级过滤器

The Auto Filter feature lets you filter from the excel interface using those tiny dropdown buttons. And the Advanced filter feature lets you filter using a criteria range.

自动过滤功能让您可以使用这些微小的下拉按钮从 Excel 界面进行过滤。高级过滤器功能让您可以使用条件范围进行过滤。

The ShowAll method removes the filters, as in, shows all the rows, but does not get rid of those Drop Down buttons. You have to set the AutoFilterMode property of the worksheet to FALSE to remove those buttons.

ShowAll 方法删除过滤器，如显示所有行，但不会删除那些下拉按钮。您必须将工作表的 AutoFilterMode 属性设置为 FALSE 才能删除这些按钮。

Here is a Sub that I use frequently to remove filters:

这是我经常用来删除过滤器的 Sub：

Sub RemoveFilters(ByRef WhichSheet As Worksheet)

If WhichSheet.FilterMode Then WhichSheet.ShowAllData
If WhichSheet.AutoFilterMode Then WhichSheet.AutoFilterMode = False

End Sub

This shows all the data, and removes the dropdown buttons. It comes in handy while stacking (copying and pasting) data from multiple sheets or workbooks. Hope this helps.

这将显示所有数据，并删除下拉按钮。它在从多个工作表或工作簿堆叠（复制和粘贴）数据时派上用场。希望这可以帮助。

回答by Linga

This will work too:

这也将起作用：

If ActiveSheet.FilterMode Then cells.AutoFilter

回答by u3397819

I usually use this code

我通常使用此代码

Sub AutoFilter_Remove()
    Sheet1.AutoFilterMode = False  'Change Sheet1 to the relevant sheet
                                   'Alternatively: Worksheets("[Your Sheet Name]").AutoFilterMode = False
End Sub