Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.programming
|
|||
|
|||
Holding off on screen updates
Is there a way to hold off on screen updates until the end of a macro, when
deleting whole rows? Here is the code: Do While Range("A" & r).Value < "" If (Range("A" & r).Value) = strOldString Then Application.DisplayAlerts = False For i = 1 To 5 Range("A" & r).EntireRow.Delete Next i Application.DisplayAlerts = True End If If IsError(Range("R" & r)) = True Then Application.DisplayAlerts = False Range("A" & r).EntireRow.Delete Application.DisplayAlerts = True ElseIf Range("R" & r).Value < 1 Then Application.DisplayAlerts = False Range("A" & r).EntireRow.Delete Application.DisplayAlerts = True Else r = r + 1 End If Loop I takes rather a long time to get through the worksheet, which starts with upwards of 3000 lines, because the display continually updates, which takes a small amount of time, but in this case those small amounts add up. Is there a way to do this without screen updating? If necessary, can I close it and then refer to the cells and ranges while it is closed? Thank You, -plh -- I keep hitting "Esc" -- but I'm still here! |
#2
Posted to microsoft.public.excel.programming
|
|||
|
|||
Holding off on screen updates
Application.ScreenUpdating = False
-- Gary''s Student "plh" wrote: Is there a way to hold off on screen updates until the end of a macro, when deleting whole rows? Here is the code: Do While Range("A" & r).Value < "" If (Range("A" & r).Value) = strOldString Then Application.DisplayAlerts = False For i = 1 To 5 Range("A" & r).EntireRow.Delete Next i Application.DisplayAlerts = True End If If IsError(Range("R" & r)) = True Then Application.DisplayAlerts = False Range("A" & r).EntireRow.Delete Application.DisplayAlerts = True ElseIf Range("R" & r).Value < 1 Then Application.DisplayAlerts = False Range("A" & r).EntireRow.Delete Application.DisplayAlerts = True Else r = r + 1 End If Loop I takes rather a long time to get through the worksheet, which starts with upwards of 3000 lines, because the display continually updates, which takes a small amount of time, but in this case those small amounts add up. Is there a way to do this without screen updating? If necessary, can I close it and then refer to the cells and ranges while it is closed? Thank You, -plh -- I keep hitting "Esc" -- but I'm still here! |
#3
Posted to microsoft.public.excel.programming
|
|||
|
|||
Holding off on screen updates
You almost answered your own question <g
Application.ScreenUpdating = False 'Your Code Application.ScreenUpdating = True or if you have a lot of formulas slowing you down: Dim CalcMode With Application CalcMode = .Calculation .Calculation = xlCalculationManual .ScreenUpdating = False .DisplayAlerts = False End With 'Your Code With Application .Calculation = CalcMode .ScreenUpdating = True .DisplayAlerts = True End With HTH Charles plh wrote: Is there a way to hold off on screen updates until the end of a macro, when deleting whole rows? Here is the code: Do While Range("A" & r).Value < "" If (Range("A" & r).Value) = strOldString Then Application.DisplayAlerts = False For i = 1 To 5 Range("A" & r).EntireRow.Delete Next i Application.DisplayAlerts = True End If If IsError(Range("R" & r)) = True Then Application.DisplayAlerts = False Range("A" & r).EntireRow.Delete Application.DisplayAlerts = True ElseIf Range("R" & r).Value < 1 Then Application.DisplayAlerts = False Range("A" & r).EntireRow.Delete Application.DisplayAlerts = True Else r = r + 1 End If Loop I takes rather a long time to get through the worksheet, which starts with upwards of 3000 lines, because the display continually updates, which takes a small amount of time, but in this case those small amounts add up. Is there a way to do this without screen updating? If necessary, can I close it and then refer to the cells and ranges while it is closed? Thank You, -plh -- I keep hitting "Esc" -- but I'm still here! |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
CAE macro for green screen updates with Excel data | Excel Discussion (Misc queries) | |||
Screen Updates on a userform | Excel Programming | |||
Screen Updates not working ? | Excel Programming | |||
Turning off all screen updates during a Solver routine | Excel Programming | |||
macros and screen updates | Excel Programming |