在 VBA 中有条件地隐藏一系列行的最快方法是什么？
声明：本页面是StackOverFlow热门问题的中英对照翻译，遵循CC BY-SA 4.0协议，如果您需要使用它，必须同样遵循CC BY-SA许可，注明原文地址和作者信息，同时你必须将它归于原作者（不是我）：StackOverFlow
原文地址: http://stackoverflow.com/questions/16823786/
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
What is the fastest way to conditionally hide a range of rows in VBA?
提问by iontom
I'm trying to speed up my VBA code and am not sure how to do it. The slowest task by far is my loop to hide empty rows. (I have several pivot tables aligned vertically and I had to leave space to allow them to expand when new data is added.)
我正在尝试加速我的 VBA 代码，但不知道该怎么做。迄今为止最慢的任务是我隐藏空行的循环。（我有几个垂直对齐的数据透视表，我不得不留出空间以允许它们在添加新数据时扩展。）
Public Sub HideRows(list1 As Range)
Dim cell As Range
For Each cell In list1
cell.EntireRow.Hidden = (cell.Value = "")
Next cell
End Sub
Public Sub UnhideRows(list1 As Range)
Dim cell As Range
For Each cell In list1
If (cell.Value <> "") Then cell.EntireRow.Hidden = False
Next cell
End Sub
回答by mr.Reband
No need to loop -- you should be able to hide the entire range all at once -- I tested using this sub:
不需要循环——你应该能够一次隐藏整个范围——我使用这个子测试：
Public Sub HideRows_test(list1 As Range)
list1.EntireRow.Hidden = True
End Sub
edit: sorry, I jumped the gun on this, didn't realize there was a condition on the range. However, instead of hiding each separately, you can build a new range using your existing loop then hide all rows in it afterwards.
编辑：抱歉，我对此不屑一顾，没有意识到靶场上有条件。但是，您可以使用现有循环构建一个新范围，然后隐藏其中的所有行，而不是分别隐藏每个范围。
edit: I mentioned you can build the range dynamically, then hide all rows at the end -- here's how:
编辑：我提到您可以动态构建范围，然后在最后隐藏所有行-方法如下：
Public Sub HideRows(list1 As Range)
Dim cellsToHide As Range
Set cellsToHide = Nothing
Dim cell As Range
For Each cell In list1
If cell.Value = "" Then
If cellsToHide Is Nothing Then
Set cellsToHide = cell
Else
Set cellsToHide = Union(cellsToHide, cell)
End If
End If
Next cell
cellsToHide.EntireRow.Hidden = True
End Sub
However, I think you can indeed get rid of the for each entirely by using SpecialCells(xlCellTypeBlanks), like so:
但是，我认为您确实可以通过使用 完全摆脱 for each SpecialCells(xlCellTypeBlanks)，如下所示：
Public Sub HideRows(list1 As Range)
Set list1 = list1.SpecialCells(xlCellTypeBlanks)
list1.EntireRow.Hidden = True
End Sub
回答by enderland
Look into using Application.ScreenUpdating. Setting this to false prevents Excel from updating the display after every operation, which increases speed significantly - ESPECIALLY when using a slow command such as hide/unhide.
考虑使用Application.ScreenUpdating. 将此设置为 false 可防止 Excel 在每次操作后更新显示，这会显着提高速度 - 特别是在使用隐藏/取消隐藏等慢速命令时。
If you don't need to iterate through your cells, you can do something similar to mr Reband's answer.
如果您不需要遍历单元格，则可以执行类似于Reband 先生的回答的操作。
But in general, Application.ScreenUpdating = falsewill speed up nearly all code interacting with Excel significantly.
但总的来说，Application.ScreenUpdating = false会显着加快几乎所有与 Excel 交互的代码。
Public Sub HideRows(list1 As Range)
Dim cell As Range
Application.ScreenUpdating = False
For Each cell In list1
cell.EntireRow.Hidden = (cell.Value = "")
Next cell
Application.ScreenUpdating = True
End Sub
Public Sub UnhideRows(list1 As Range)
Dim cell As Range
Application.ScreenUpdating = False
For Each cell In list1
If (cell.Value <> "") Then cell.EntireRow.Hidden = False
Next cell
Application.ScreenUpdating = True
End Sub
