更快地隐藏空行

I am trying to hide all rows where the value of 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 VLOOKUP formula and the xlCellTypeBlanks considers a cell with a formula, but no value, not to 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?

我怎么加快速度?

4 个解决方案

#1

Why don't you try AutoFilter:

为什么不试试自动滤镜:

Range("A7:A117").AutoFilter 1, "<>", , , False

#2

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

#3

Range("A7:A117").AutoFilter 1, "<>", , , False

It hides empty cells but if you try to unhide with mouse you cannot

它会隐藏空的单元格，但是如果你试图用鼠标解除隐藏，那你就不能

#4

Here's an answer without 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

#1