Faster way to hide blank lines

Question

I am trying to hide all rows where the cell value in column A is empty (i.e. empty). I tried to use the following code:

Range("A7:A117").SpecialCells(xlCellTypeBlanks).EntireRow.Hidden = True

However, each cell in column A has a formula VLOOKUP, and xlCellTypeBlanksconsiders a cell with a formula, but it does not matter, it should not be empty.

So I tried to use the following code, but it is very slow.

For i = 17 To 117
  If ActiveSheet.Cells(i, 1) = "" Then
    ActiveSheet.Cells(i, 1).EntireRow.Hidden = True
End If

How to speed it up?

Answer 1

Why don't you try AutoFilter:

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

Answer 2

for, , , , - ( ). , , , , script . 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

Answer 3

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

, ,

Answer 4

:

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