Excel 2013 VBA清除活动过滤器 [英] Excel 2013 VBA clear active filter
问题描述
我需要先清除工作表中的所有活动过滤器,然后再运行某个宏,如果启用了活动过滤器,此行就可以正常工作
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
我从这个问题的答案中得到了代码 Excel 2013 VBA清除所有过滤器宏
I got that code from an answer to this question 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?
编辑
例如,所有列标题均已自动过滤,因此,例如,如果我的工作表已由女性"过滤,则需要在运行宏之前先删除该过滤器,但是,如果未应用任何过滤器,只需将宏运行为正常
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
推荐答案
使用FilterMode代替AutoFilterMode.我经常处理过滤器,此代码可以正常工作.
Use FilterMode instead of AutoFilterMode. I have dealt with filters frequently and this code works fine.
If ActiveSheet.FilterMode Then
ActiveSheet.ShowAllData
End If
确保工作表不受保护,因为这也会产生1004错误.
Make sure the worksheet is not protected as this also gives the 1004 error.
这篇关于Excel 2013 VBA清除活动过滤器的文章就介绍到这了,希望我们推荐的答案对大家有所帮助,也希望大家多多支持IT屋!