Slow macro hide/unhiding rows

YevCollins · Jul 28th 2016

Dear all,

I've built a macro which task is simply to hide/unhide a few hundred rows in a single worksheet in Excel and hide/unhide 2 out of 4 other worksheets. This takes unfortunately quite some time (about 30 seconds - one minute).
Is there a way to make it run faster? I have used some tips on this and other forums, which improved the running time a bit (especially turning off the Automatic Calculation function) but it is still pretty slow.

I'll put the code below

Thanks in advance!

Kind regards,
Jef

Code

Sub PresentationAxe()    
Application.ScreenUpdating = False
Application.EnableEvents = False
ActiveSheet.DisplayPageBreaks = False




'
' PresentationAxe Macro
Application.Calculation = xlManual




 'Load waiting message
    UFWiP.Show 0
    UFWiP.Repaint
    Application.ScreenUpdating = False
    
' presentation code
    Rows("31:217").Select
    Selection.EntireRow.Hidden = True
    Rows("218:403").Select
    Selection.EntireRow.Hidden = False
    
Sheets("Res-ENJEU").Visible = False
Sheets("Res-AXE").Visible = True
Sheets("Budget-ENJEU").Visible = False
Sheets("Budget-AXE").Visible = True
    Application.Calculation = xlAutomatic




    Unload UFWiP
    
Application.ScreenUpdating = True
Application.EnableEvents = True




End Sub

Display More

cytop · Jul 28th 2016

Re: Slow macro hide/unhiding rows

Small point - there's no need to select a range before working with it...

Code

Rows("31:217").EntireRow.Hidden = True 
Rows("218:403").EntireRow.Hidden = False

You can aslo hide multiple sheets at the same time.

Code

sheets(Array("Res-ENJEU", "Budget-ENJEU")).visible = False

But that code shouldn't take that long. Guess either a Recalc when you set the Calculation mode back to automatic might cause it, or some other event is being fired that takes the time.

Whichever, it's hard to suggest without seeing a copy of the workbook.

[sw]*[/sw]

Slow macro hide/unhiding rows

Participate now!

Share