Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.programming
|
|||
|
|||
Speed Up Macro
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 |
#2
Posted to microsoft.public.excel.programming
|
|||
|
|||
Speed Up Macro
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 "Sean" ... 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 |
#3
Posted to microsoft.public.excel.programming
|
|||
|
|||
Speed Up Macro
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 -- Regards, Tom Ogilvy "Sean" wrote: 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 |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
Help, need to speed up this macro | Excel Discussion (Misc queries) | |||
How to speed up this macro? | Excel Programming | |||
Speed-up a macro! | Excel Programming | |||
Speed up macro | Excel Discussion (Misc queries) | |||
MACRO Speed? | Excel Programming |