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!

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!

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!