Speed Up Macro

[Guest]

I have a macro that takes over 45 min to run at times. Is there a better way
to do this, or speed it up at all. The macro I use is as follows, it does
need to be specific to the column and the rows that it is currently set to...

Sub Button15_Click()
ActiveSheet.Unprotect
For Each cell In Range("AL35:AL609")
cell.EntireRow.Hidden = cell.Value = 0
Next cell
ActiveSheet.Protect DrawingObjects:=True, Contents:=True, Scenarios:=True
End Sub

Thanks,

Sean

 

[NickHK]

Sean,
Are you sure there is nothing else happening, because I cannot see how that
code take 45 minutes to run ?
Setting .displaypageBreaks=False would, but still...you could do that code
manulally in 5 minutes.

NickHK

 

[Guest]

Sub Button15_Click()
ActiveSheet.Unprotect

calc = Application.Calculation
Application.Calculation = xlManual
ActiveSheet.DisplayAutomaticPageBreaks = False
For Each cell In Range("AL35:AL609")
cell.EntireRow.Hidden = cell.Value = 0
Next cell
Application.Calculation = calc
ActiveSheet.Protect DrawingObjects:=True, Contents:=True, Scenarios:=True
End Sub

or

Sub Button15_Click()
Dim rng as Range
ActiveSheet.Unprotect

calc = Application.Calculation
Application.Calculation = xlManual
ActiveSheet.DisplayAutomaticPageBreaks = False
Range("AL35:AL609").EntireRow.Hidden = False
For Each cell In Range("AL35:AL609")
if cell.Value = 0 then
if rng is nothing then
set rng = cell
else
set rng = Union(rng,cell)
end if
End if
Next cell
if not rng is nothing then
rng.entireRow.Hidden = True
End if
Application.Calculation = calc
ActiveSheet.Protect DrawingObjects:=True, Contents:=True, Scenarios:=True
End Sub