在 Excel VBA 中有效隐藏/取消隐藏许多 (+500) 行
声明：本页面是StackOverFlow热门问题的中英对照翻译，遵循CC BY-SA 4.0协议，如果您需要使用它，必须同样遵循CC BY-SA许可，注明原文地址和作者信息，同时你必须将它归于原作者（不是我）：StackOverFlow
原文地址: http://stackoverflow.com/questions/18160023/
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
Efficiently hiding / unhiding many (+500) rows in Excel VBA
提问by Richard Sanders
Currently I am working on a Excel/VBA workbook that displays approx. 500 items in of a budget, each on a different row. I want the user to select the level of detail in the budget: on the highest level each individual item will be show, on the lower level of detail several items will be grouped under a single header.
目前我正在处理一个显示大约的 Excel/VBA 工作簿。预算中有 500 个项目，每个项目在不同的行中。我希望用户选择预算中的详细级别：在最高级别上将显示每个单独的项目，在较低的详细级别上将几个项目分组到一个标题下。
The method outlined below (hiding/unhiding line-by-line) is very slow: it takes around 4 minutes to hide / unhide most of the items. I hope you have any hints for me how to speed up the programm!
下面概述的方法（逐行隐藏/取消隐藏）非常慢：隐藏/取消隐藏大部分项目大约需要 4 分钟。我希望你对我如何加速程序有任何提示！
When selecting a level of detail via a dialog box, the program runs through the following steps:
通过对话框选择详细程度时，程序将执行以下步骤：
1) Updating screen is set off:
1) 更新画面被关闭：
Application.ScreenUpdating = False
Application.EnableEvents = False
2) Per line item in the budget it is determined whether that row should be hidden or not. The code I am using for this is the following:
2) 预算中的每个行项目确定是否应隐藏该行。我为此使用的代码如下：
with sheets("[name sheet here]")
.Range("[identifier of budget line item here]").EntireRow.Hidden = False / True
...
[500 times]
...
end with
3) Second, depending on level of detail, the text corresponding to a sub-header is made either white or black. This is done for around 20 rows:
3) 其次，根据详细程度，与子标题对应的文本要么是白色的，要么是黑色的。这是为大约 20 行完成的：
With Sheets("[name sheet here]").Range("[identifier of budget line item here]").Font
.ThemeColor = xlThemeColorLight1
.TintAndShade = 0
End With
4) Lastly, again depending on level of detail, some lines are drawn again that become invisible when hiding some rows. This is done 10 times or so for various ranges:
4) 最后，同样根据细节级别，一些线会再次绘制，当隐藏一些行时这些线变得不可见。对于各种范围，这将完成 10 次左右：
Range("[range here]").Select
Selection.Borders(xlDiagonalDown).LineStyle = xlNone
Selection.Borders(xlDiagonalUp).LineStyle = xlNone
Selection.Borders(xlEdgeLeft).LineStyle = xlNone
Selection.Borders(xlEdgeTop).LineStyle = xlNone
With Selection.Borders(xlEdgeBottom)
.LineStyle = xlContinuous
.ColorIndex = 0
.TintAndShade = 0
.Weight = xlThin
End With
回答by chuff
Just some general thoughts.
只是一些一般的想法。
Apply an autofilter to the data range, with the user-supplied detail level used as the criterion for the filtering. If you don't want the result to look like an autofilter, you can programmatically hide the dropdown arrow at the top of each column. The method is described here.
If you don't want the user to be presented with an autofilter, you can still use it to do the filtering. The steps would be: use autofilter to filter out (not show) the rows you do notwant to hide; assign the filtered rows to a range variable; remove the filter; hide in bulk the rows identified in the range variable. See this SO postfor a working example of the technique.
Loop through the list using the Union function to gather all the rows you want to hide into a single range object and then hide the rows by setting the IsHidden property True for the rows in the range (HideRange.EntireRow.IsHidden = True). (See herefor an example of the approach, which in that case is used to delete selected rows.)
对数据范围应用自动过滤器，将用户提供的详细程度用作过滤标准。如果您不希望结果看起来像一个自动过滤器，您可以通过编程方式隐藏每列顶部的下拉箭头。此处描述了该方法。
如果您不希望向用户显示自动过滤器，您仍然可以使用它来进行过滤。具体的步骤是：使用自动筛选过滤掉（不显示），你就行不会要隐藏; 将过滤后的行分配给范围变量；取下过滤器；批量隐藏范围变量中标识的行。有关该技术的工作示例，请参阅此 SO 帖子。
使用 Union 函数循环遍历列表以将所有要隐藏的行收集到单个范围对象中，然后通过为该范围内的行设置 IsHidden 属性 True 来隐藏这些行 (HideRange.EntireRow.IsHidden = True)。（有关该方法的示例，请参见此处，在这种情况下，该方法用于删除选定的行。）
Any of these techniques will take much less than 4 minutes to hide the rowsl.
这些技术中的任何一种都将花费不到 4 分钟的时间来隐藏 rowl。
回答by neizan
Why not just hide larger ranges of rows instead of one row at a time? Like
为什么不只隐藏更大范围的行而不是一次隐藏一行？喜欢
Sub HideRows()
ActiveSheet.Range(Cells(1, 1), Cells(500, 1)).EntireRow.Hidden = True
End Sub
Sub UnHideRows()
ActiveSheet.Range(Cells(1, 1), Cells(500, 1)).EntireRow.Hidden = False
End Sub
