Is there a way to speed up this code?

Captain Smith

Active Member
Joined
Feb 28, 2003
Messages
324
Is there a better way to write this code so it goes faster? Right now it takes approx 2 minutes to complete. Thank you.

Sub Macro5()
Dim cell As Range
Application.ScreenUpdating = False
For Each cell In Range("L1:L3000")
cell.FormulaR1C1 = "=IF(Consolidation!R[7]C[-3]="""","""",Consolidation!R[7]C[-3])"
cell.Offset(0, 1).FormulaR1C1 = "=IF(Consolidation!R[7]C[-3]="""","""",Consolidation!R[7]C[-3])"
cell.Offset(0, 2).FormulaR1C1 = "=IF(Consolidation!R[7]C[-3]="""","""",Consolidation!R[7]C[-3])"
cell.Offset(0, 3).FormulaR1C1 = "=IF(Consolidation!R[7]C[-3]="""","""",Consolidation!R[7]C[-3])"
cell.Offset(0, 4).FormulaR1C1 = "=IF(Consolidation!R[7]C[-3]="""","""",Consolidation!R[7]C[-3])"
cell.Offset(0, 5).FormulaR1C1 = "=IF(Consolidation!R[7]C[-3]="""","""",Consolidation!R[7]C[-3])"
cell.Offset(0, 6).FormulaR1C1 = "=IF(Consolidation!R[7]C[-3]="""","""",Consolidation!R[7]C[-3])"
cell.Offset(0, 7).FormulaR1C1 = "=IF(Consolidation!R[7]C[-3]="""","""",Consolidation!R[7]C[-3])"
cell.Offset(0, 8).FormulaR1C1 = "=IF(Consolidation!R[7]C[-3]="""","""",Consolidation!R[7]C[-3])"
Next cell
Application.ScreenUpdating = True
MsgBox ("done")
End Sub
 

Excel Facts

Bring active cell back into view
Start at A1 and select to A9999 while writing a formula, you can't see A1 anymore. Press Ctrl+Backspace to bring active cell into view.

Weaver

Well-known Member
Joined
Sep 10, 2008
Messages
5,197
try

Code:
Range("L1:L3000").FormulaR1C1 = "=IF(Consolidation!R[7]C[-3]="""","""",Consolidation!R[7]C[-3])"
 
Upvote 0

NateO

Legend
Joined
Feb 17, 2002
Messages
9,700
This runs in less than a second on my machine (XL 2007):

Code:
Sub foo()
Dim clcMode As Long
With Application
    .ScreenUpdating = False
    Let clcMode = .Calculation
    .Calculation = xlCalculationManual
End With

Let Range("L1:T3000").FormulaR1C1 = _
    "=IF(Consolidation!R[7]C[-3]="""","""",Consolidation!R[7]C[-3])"

With Application
    .ScreenUpdating = True
    .Calculation = clcMode
End With

MsgBox ("done")
End Sub
You're probably throttling the Calc Engine...

Let it happen, Captain! :biggrin:
 
Upvote 0

Weaver

Well-known Member
Joined
Sep 10, 2008
Messages
5,197
Oops of course - forgot to change it after I copied & pasted from the original post. Cheers

Code:
Range("L1:T3000").FormulaR1C1 = "=IF(Consolidation!R[7]C[-3]="""","""",Consolidation!R[7]C[-3])"
 
Upvote 0

NateO

Legend
Joined
Feb 17, 2002
Messages
9,700
You're welcome. It also occurs to me that changing this:

Code:
With Application
    .ScreenUpdating = True
    .Calculation = clcMode
End With

To this:

Code:
With Application
    .Calculation = clcMode
    .ScreenUpdating = True
End With
Would be potentially faster, to recalc before you redraw the screen. :)
 
Upvote 0

Forum statistics

Threads
1,190,629
Messages
5,982,020
Members
439,750
Latest member
megaman777

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Top