vba 当单元格为空时隐藏行 excel（更快的方式）
声明：本页面是StackOverFlow热门问题的中英对照翻译，遵循CC BY-SA 4.0协议，如果您需要使用它，必须同样遵循CC BY-SA许可，注明原文地址和作者信息，同时你必须将它归于原作者（不是我）：StackOverFlow
原文地址: http://stackoverflow.com/questions/27830796/
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
Hide Rows when cell is empty excel (faster way)
提问by Tomek
I had a look at some articles and suggestions and came up with a solution for my problem.
我查看了一些文章和建议，并为我的问题提出了解决方案。
The article Faster way to hide empty rowssuggested to use to toggle Application.ScreenUpdatingbefore the loop to false and after to true. It sped up the script a little but for 10.000 rows it still takes quite some time.
文章更快的隐藏空行的方法建议用于Application.ScreenUpdating在循环之前切换到 false 和之后切换到 true。它加快了脚本的速度，但对于 10.000 行，它仍然需要相当长的时间。
Requirements:
要求：
If the first cell in the row is empty, hide the complete row it needs to be backwards compatible to other excel versions than 2013
如果行中的第一个单元格为空，则隐藏整行它需要向后兼容 2013 以外的其他 excel 版本
Current working solution for excel 2013
excel 2013 的当前工作解决方案
Application.ScreenUpdating = False
Dim s As String
For i = 1 To range("A1:A10000").Count
s = i & ":" & i
If IsEmpty(Cells(i, 1).Value) Then
Rows(s).Select
Selection.EntireRow.Hidden = True
End If
Next
Application.ScreenUpdating = True
Unfortunately I don't know how much data in the sheet is, it can contain less than 10.000 which i used in my example or even more. When i run the script it works fine, but takes quite a while for 10.000 rows and ages for all cells in the sheet.
不幸的是，我不知道工作表中有多少数据，它包含的数据可能少于我在示例中使用的 10.000，甚至更多。当我运行脚本时，它工作正常，但是对于工作表中的所有单元格，10.000 行和年龄需要相当长的时间。
The macro would run on automatically when the workbook is loaded (not sure yet how to do that either haha)
加载工作簿时，宏将自动运行（不确定如何执行，哈哈）
采纳答案by Tomek
As in my comments and in this post: Faster way to hide empty rowsand thanks to @tigeravatar for pointing it out. I used the following code which worked in my scenario:
正如我的评论和这篇文章：更快的方法来隐藏空行并感谢@tigeravatar 指出它。我使用了在我的场景中工作的以下代码：
Range("A1:A10000").AutoFilter 1, "<>", , , False
回答by Tim Williams
Dim rngBlnk As Range
On Error Resume Next 'in case no blanks are present...
Set rngBlnk = Range("A1:A100000").SpecialCells(xlCellTypeBlanks)
On Error GoTo 0
If Not rngBlnk Is Nothing Then
Debug.Print rngBlnk.Address()
rngBlnk.EntireRow.Hidden = True
End If
回答by Neil Humphries
The select process takes time, and there is no need to select the row before hiding the row. Try the variation below...
选择过程需要时间，在隐藏行之前不需要选择行。试试下面的变化...
Application.ScreenUpdating = False
Dim s As String
For i = 1 To range("A1:A10000").Count
s = i & ":" & i
If IsEmpty(Cells(i, 1).Value) Then
Rows(s).EntireRow.Hidden = True
End If
Next
Application.ScreenUpdating = True
