Hide Row code running slow

Question

Hi All......

Excel 2010, Windows XP and Windows 7.

I have a macro that has worked fine for years in hiding rows in a column if there is a value in a cell.  All of a sudden it has started running painfully slow, taking several seconds to process the Cells(r, "au").EntireRow.Hidden = True statement.  This occurs on several different computers.

Sub HideRowIfCompleted()

Dim lastrow As Long, r As Long

Sheets("WorkOrders").Select

lastrow = Cells(Rows.Count, "i").End(xlUp).Row

Range("a3").Select

Application.ScreenUpdating = False

  For r = lastrow To 12 Step -1

      If Cells(r, "Au") <> "" Then

          Cells(r, "au").EntireRow.Hidden = True

      End If

  Next r

  Application.ScreenUpdating = True

End Sub

Any help would be much appreciated.

Vaya con Dios,

Chuck, CABGx3

Answer 1

A quick way would be to insert after Application.ScreenUpdating = False

Application.DisplayStatusBar = False

Application.Calculation = xlCalculationManual

Application.EnableEvents = False

ActiveSheet.DisplayPageBreaks = False

And after Application.ScreenUpdating = True

Application.DisplayStatusBar = True

Application.Calculation = xlCalculationAutomatic

Application.EnableEvents = True

ActiveSheet.DisplayPageBreaks = True

Let's us know if improvement is there or not.

Answer 2

Thanks again Vijay, but that is a bit above my Pay-Grade, lol.

The original macro I came here about was actually a SubRoutine to a Master macro that also contained several other Subs that had similar actions.  The Master crashed with each of the Subs for apparently the same reason, and when I applied your fix to all the Subs it all seems to run fine now.

This is a large workbook with a LOT of macros.  Perhaps I should go through it and doctor up EVERY instance of "Application.ScreenUpdating = False" that I have in it just to be on the safe side...........what say you?

Regards,

Chuck, CABGx3

Answer 3

No idea about that as situation is very unique. Another tip for you for future in case you need -

To further speed up, we can hide rows in one go rather than one by one. You can replace your code with following -

'**** Vijay A. Verma (eforexcel.com) ***********

Sub xyz()

Dim lastrow As Long, r As Long

Sheets("WorkOrders").Select

lastrow = Cells(Rows.Count, "I").End(xlUp).Row

Range("a3").Select

Application.ScreenUpdating = False

Application.DisplayStatusBar = False

Application.Calculation = xlCalculationManual

Application.EnableEvents = False

ActiveSheet.DisplayPageBreaks = False

Dim Rng As Range

  For r = lastrow To 1 Step -1

      If Cells(r, "Au") <> "" Then

       If Rng Is Nothing Then

      Set Rng = Cells(r, "Au")

      Else

      Set Rng = Union(Rng, Cells(r, "Au"))

      End If

    End If    

  Next r

  Rng.EntireRow.Hidden = True

 Application.ScreenUpdating = True

Application.DisplayStatusBar = True

Application.Calculation = xlCalculationAutomatic

Application.EnableEvents = True

ActiveSheet.DisplayPageBreaks = True

End Sub

Answer 4

Many many thanks Vijay.......your solution worked perfectly!

Any idea what caused the issue to happen all of a sudden after so many years of working?

Regards,

Chuck, CABGx3