Speeding up macro

mike_ate_a_pie

Board Regular
Joined
Sep 25, 2009
Messages
69
I have the following code that hides any row within the range that is zero. However, there is a around a 10 second delay when clicking onto the sheet. I have tried using screen updating but there is still this delay. Does anyone know what i can do to avoid this.

Thanks

Private Sub Workbook_SheetActivate(ByVal Sh As Object)

Application.ScreenUpdating = False

Dim arrSheets()
Dim lngRowNum As Long

arrSheets = Array("Bold", "Heatons")
If Not IsError(Application.Match(Sh.Name, arrSheets, 0)) Then
For lngRowNum = 6 To 55
Rows(lngRowNum).Hidden = Cells(lngRowNum, 45).Value = 0
Next lngRowNum
End If

Application.ScreenUpdating = True

End Sub
 

Some videos you may like

Excel Facts

Bring active cell back into view
Start at A1 and select to A9999 while writing a formula, you can't see A1 anymore. Press Ctrl+Backspace to bring active cell into view.

Andrew Poulsom

MrExcel MVP
Joined
Jul 21, 2002
Messages
73,092
For me that code is instantaneous. Maybe try setting calculation to manual at the beginning and to automatic at the end.
 

Watch MrExcel Video

Forum statistics

Threads
1,122,471
Messages
5,596,350
Members
414,060
Latest member
hermanseck

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Top