Speed up VBA Code to Hide Rows

[tlc53]

Hi there,

This VBA code has been super useful in many spreadsheets but on this occasion, it is running very slow. It takes 10 minutes to complete!
The reason is that my range is usually only about 15 cells but now it needs to check 100 cells.

Is there any way to improve/speed up this code?

Thank you!

Sub HideRows()
   Dim Cl As Range
   For Each Cl In Range("D21:D120")
     Cl.EntireRow.Hidden = IIf(Cl = 0, True, False)
   Next Cl
End Sub

 

[Fluff]

That code should take well under a second, even if hiding all the rows.
However you can get rid of the IIF

Sub HideRows()
   Dim Cl As Range
   For Each Cl In Range("D21:D120")
     Cl.EntireRow.Hidden = Cl = 0
   Next Cl
End Sub

Do you have lots of formulae on that sheet?

 

[tlc53]

That code should take well under a second, even if hiding all the rows.
However you can get rid of the IIF

Sub HideRows()
   Dim Cl As Range
   For Each Cl In Range("D21:D120")
     Cl.EntireRow.Hidden = Cl = 0
   Next Cl
End Sub

Do you have lots of formulae on that sheet?

Yes, there are lots of formulas but I amended the code as above and it ran super fast. About 20 seconds!!
Thank you!

 

[Fluff]

If you turn calculation, it might speed it up a bit more.

Sub HideRows()
   Dim Cl As Range, t
   Application.Calculation = xlCalculationManual
   For Each Cl In Range("D21:D120")
     Cl.EntireRow.Hidden = Cl = 0
   Next Cl
   Application.Calculation = xlCalculationAutomatic
End Sub

 

[tlc53]

If you turn calculation, it might speed it up a bit more.

Sub HideRows()
   Dim Cl As Range, t
   Application.Calculation = xlCalculationManual
   For Each Cl In Range("D21:D120")
     Cl.EntireRow.Hidden = Cl = 0
   Next Cl
   Application.Calculation = xlCalculationAutomatic
End Sub

Holy heck! This code blew the other code out the window ? Thank you!

 

[Michael M]

You could also turn off screenupdating

Sub HideRows()
   Dim Cl As Range, t
with Application
     .Calculation = xlCalculationManual
     .screenupdating=false
end with
   For Each Cl In Range("D21:D120")
     Cl.EntireRow.Hidden = Cl = 0
   Next Cl
with Application
     .Calculation = xlCalculationautomatic
     .screenupdating=true
end with
End Sub

 

[Fluff]

Holy heck! This code blew the other code out the window ? Thank you!

You're welcome & thanks for the feedback.