使用 VBA 清除工作表上的所有过滤器时速度非常慢
声明：本页面是StackOverFlow热门问题的中英对照翻译，遵循CC BY-SA 4.0协议，如果您需要使用它，必须同样遵循CC BY-SA许可，注明原文地址和作者信息，同时你必须将它归于原作者（不是我）：StackOverFlow
原文地址: http://stackoverflow.com/questions/15807234/
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
Extremely slow when clearing all filters on a sheet using VBA
提问by Matteous
I don't understand why this is taking so long to complete, strangely enough it takes the most time in executing "ActiveSheet.ShowAllData". Can anyone suggest what may improve it?
我不明白为什么要花这么长时间才能完成，奇怪的是它在执行“ActiveSheet.ShowAllData”时花费了最多的时间。谁能建议什么可以改善它？
Sub ClearAllFilters()
Application.Calculation = xlManual
Application.ScreenUpdating = False
If ActiveSheet.FilterMode Then
ActiveSheet.ShowAllData
For Each OleObj In ActiveSheet.OLEObjects
If OleObj.progID = "Forms.CheckBox.1" Then
OleObj.Object = False
End If
Next
Sheets("Manhour Summary Current Month").TextBox1.Text = ""
Sheets("Manhour Summary Current Month").TextBox2.Text = ""
Sheets("Manhour Summary Current Month").TextBox3.Text = ""
ActiveSheet.Range("$A:$H07").AutoFilter Field:=8, Criteria1:="<>0"
End If
Application.Calculation = xlAutomatic
Application.ScreenUpdating = True
End Sub
Basically I'm clearing all autofilters, clearing a checkbox and clearing three textboxes. This is for ~1000 rows of data, so it's nothing excessive I would've thought!
基本上我正在清除所有自动过滤器，清除复选框并清除三个文本框。这是大约 1000 行数据，所以我没想到这并不过分！
Thank you!
谢谢！
The problem was with conditional formatting applied to the majority of cells. I'm astounded that it's such a problem to have autofilters and conditional formatting.
问题在于应用于大多数单元格的条件格式。我很惊讶拥有自动过滤器和条件格式是一个如此大的问题。
Massive thank you for all your help! My head feels a lot better now....
非常感谢您的帮助！现在我的头感觉好多了......
采纳答案by glh
I prefer to use ActiveSheet.AutoFilterMode = Falseand I'd be interested to see if this improve things.
我更喜欢使用ActiveSheet.AutoFilterMode = False，我很想看看这是否会有所改善。
added performance potential:
增加的性能潜力：
If the opportunity is there flattering your formulas is ideal as you have so many.
If these are required but you can update at request, say when times are updated, then a trick I've learnt is keep the first row as formulas but flatten the rest and when the data is updated then copy these formulas down, update the values and then re-flatten all but the first row again.
Alternative to the above is to create a procedure that updates these values and have this run when needed and have no formulas at all.
如果有机会奉承你的公式是理想的，因为你有这么多。
如果这些是必需的，但您可以根据要求进行更新，例如更新时间，那么我学到的一个技巧是将第一行保留为公式，但将其余部分展平，当数据更新时，将这些公式复制下来，更新值然后再次重新展平除第一行之外的所有内容。
上述替代方法是创建一个更新这些值的过程，并在需要时运行此过程，并且根本没有公式。
Post conditional formatting issue:
后条件格式问题：
As a way to improve due to conditional formatting you could have the sheet change the cell format once data has been updated via VBA instead. This will reduce excel doing this every time you filter.
Also and possibly my favouritedue to your circumstances is to have your dates display the day, say
Sat 2-Mar-13. This will still give the quick check of a weekend and improve performance.
作为一种由于条件格式而进行改进的方法，您可以在通过 VBA 更新数据后让工作表更改单元格格式。这将减少每次过滤时执行此操作的 excel。
此外，由于您的情况，我可能最喜欢的是让您的日期显示当天，例如
Sat 2-Mar-13。这仍然可以快速检查周末并提高性能。
