Slow VBA macro

R

R Ormerod

Every time the following code runs and it reaches a blank cell it takes 2-3
seconds to update the cell with a zero:

Do Until ActiveCell.Offset(1, -3) = ""

ActiveCell.Offset(1, 0).Range("A1").Select

Select Case ActiveCell
Case Is = ""
Selection.FormulaR1C1 = 0
End Select

Loop

I've tried Application.ScreenUpdating = False but the macro is still very
slow.

Any suggestions as to how I might speed this up?
 
B

Bob Phillips

TRy this

Application.Calculation = xlCalculationManual
i = 0
With Activecell
Do Until .Offset(i+1, -3) = ""
Select Case.Offset(i+1, 0).Range("A1")
Case Is = ""
.Value = 0
End Select
i = i+1
Loop
Application.Calculation = xlCalculationAutomatic

--

HTH

Bob Phillips
... looking out across Poole Harbour to the Purbecks
(remove nothere from the email address if mailing direct)
 
Top