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

Why are there 1,048,576 rows in Excel?
The Excel team increased the size of the grid in 2007. There are 2^20 rows and 2^14 columns for a total of 17 billion cells.
try

Code:
Range("L1:L3000").FormulaR1C1 = "=IF(Consolidation!R[7]C[-3]="""","""",Consolidation!R[7]C[-3])"
 
Upvote 0
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
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
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,213,529
Messages
6,114,155
Members
448,554
Latest member
Gleisner2

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
Back
Top