Excel 2013 VBA 清除活动过滤器
声明：本页面是StackOverFlow热门问题的中英对照翻译，遵循CC BY-SA 4.0协议，如果您需要使用它，必须同样遵循CC BY-SA许可，注明原文地址和作者信息，同时你必须将它归于原作者（不是我）：StackOverFlow
原文地址: http://stackoverflow.com/questions/28086408/
Warning: these are provided under cc-by-sa 4.0 license. You are free to use/share it, But you must attribute it to the original authors (not me):
StackOverFlow
Excel 2013 VBA clear active filter
提问by Tim Wilkinson
I need to clear any active filters from a sheet before running a certain macro, this line works just fine IF there is an active filter on
在运行某个宏之前，我需要清除工作表中的所有活动过滤器，如果有活动过滤器，这条线就可以正常工作
If ActiveSheet.AutoFilterMode Then ActiveSheet.ShowAllData
However if no filters are selected it returns the error
但是，如果未选择任何过滤器，则会返回错误
Runtime error '1004';
ShowAllData method of Worksheet class failed
I got that code from an answer to this question Excel 2013 VBA Clear All Filters macro
我从这个问题Excel 2013 VBA Clear All Filters macro的答案中得到了该代码
However that question doesn't explain how to ignore the line if no filters are active.
但是，如果没有过滤器处于活动状态，该问题并没有解释如何忽略该行。
How do I ignore this line if there are no currently active filters applied?
如果当前没有应用活动过滤器，如何忽略此行？
EDIT
编辑
For example, all column headings have been auto filtered, so if my sheet is filtered by 'Female' for example I need to remove that filter before running the macro, however if no filters have been applied, just run the macro as normal
例如，所有列标题都已自动过滤，因此如果我的工作表被“女性”过滤，例如我需要在运行宏之前删除该过滤器，但是如果没有应用过滤器，只需正常运行宏
回答by Paul Kelly
Use FilterMode instead of AutoFilterMode. I have dealt with filters frequently and this code works fine.
使用 FilterMode 而不是 AutoFilterMode。我经常处理过滤器，这段代码工作正常。
If ActiveSheet.FilterMode Then
ActiveSheet.ShowAllData
End If
Make sure the worksheet is not protected as this also gives the 1004 error.
确保工作表不受保护，因为这也会导致 1004 错误。
回答by Paul Kelly
I sincerely admire your desire to program for specific circumstances but I have to admit that the quickest way to accomplish this is with On Error Resume Next.
我真诚地钦佩您为特定情况编程的愿望，但我必须承认，实现这一目标的最快方法是使用On Error Resume Next.
On Error Resume Next
ActiveSheet.ShowAllData
On Error GoTo 0
You shouldn't have to break something to check if it exists but in VBA that is occasionally the best recourse. Personally, I rank On Error Resume Nextright up there with SendKeysas a programming method.
你不应该打破某些东西来检查它是否存在，但在 VBA 中，这有时是最好的追索权。就个人而言，我作为一种编程方法On Error Resume Next在那里排名第一SendKeys。
The above method does not require that you check to see if .AutoFilterModeis True.
上述方法不需要您检查是否.AutoFilterMode为True。
回答by Shadmage
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
** Possible duplicate of thread: Excel 2013 VBA Clear All Filters Macro
** 线程可能重复：Excel 2013 VBA 清除所有过滤器宏
