Faster way to hide empty rows

Question

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:

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.

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?

Solution 1

Why don't you try AutoFilter:

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

Solution 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.

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