vba 隐藏空行的更快方法
声明：本页面是StackOverFlow热门问题的中英对照翻译，遵循CC BY-SA 4.0协议，如果您需要使用它，必须同样遵循CC BY-SA许可，注明原文地址和作者信息，同时你必须将它归于原作者（不是我）：StackOverFlow
原文地址: http://stackoverflow.com/questions/24078437/
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
Faster way to hide empty rows
提问by user3450844
I am trying to hide all rows where the valueof the cell in Column A is blank (i.e. empty). I was trying to use the following code:
我想隐藏的所有行的值在列A的细胞是空白的（即空）。我试图使用以下代码：
Range("A7:A117").SpecialCells(xlCellTypeBlanks).EntireRow.Hidden = True
However, every cell in Column A has a VLOOKUPformula and the xlCellTypeBlanksconsiders a cell with a formula, but no value, notto be blank.
但是，A 列中的每个单元格都有一个VLOOKUP公式，并且xlCellTypeBlanks认为带有公式但没有值的单元格不是空白。
So I tried using the following code, but it is extremely slow.
所以我尝试使用以下代码，但速度非常慢。
For i = 17 To 117
If ActiveSheet.Cells(i, 1) = "" Then
ActiveSheet.Cells(i, 1).EntireRow.Hidden = True
End If
How do I speed it up?
我如何加快速度？
回答by L42
Why don't you try AutoFilter:
你为什么不试试自动过滤：
Range("A7:A117").AutoFilter 1, "<>", , , False
回答by D Mason
It is not the for loop that is slow it is that you are updating the screen everytime something changes (this uses a fair bit of processing power and thus slows everything down). if you turn screen updating off before you hide the rows then turn it back on after it will only update once and the script will run much much faster. I tried it with 100 rows and it was almost instant.
缓慢的不是 for 循环，而是每次发生变化时您都在更新屏幕（这会使用相当多的处理能力，因此会减慢一切）。如果在隐藏行之前关闭屏幕更新，则在它只更新一次之后重新打开它，脚本将运行得更快。我尝试了 100 行，几乎是即时的。
Sub hideEmptyRows()
Application.ScreenUpdating = False
For i = 1 To 117
If ActiveSheet.Cells(i, 1) = "" Then
ActiveSheet.Cells(i, 1).EntireRow.Hidden = True
End If
Next i
Application.ScreenUpdating = True
End Sub
回答by Servlet
Range("A7:A117").AutoFilter 1, "<>", , , False
It hides empty cells but if you try to unhide with mouse you cannot
它隐藏了空单元格，但如果您尝试使用鼠标取消隐藏，则无法
回答by Seb
Here's an answer without Autofilter :
这是没有 Autofilter 的答案：
Dim totalRange As Range
ActiveSheet.Range("A17:A117").Hidde = false
For Each cell In ActiveSheet.Range("A17:A117")
If cell = "" And totalRange Is Nothing Then
Set totalRange = cell
ElseIf cell = "" Then
Set totalRange = Application.union(totalRange, cell)
End If
Next
If Not totalRange Is Nothing Then
totalRange.EntireRow.Hidden = True
End If
