try
Code:Range("L1:L3000").FormulaR1C1 = "=IF(Consolidation!R[7]C[-3]="""","""",Consolidation!R[7]C[-3])"
This is a discussion on Is there a way to speed up this code? within the Excel Questions forums, part of the Question Forums category; Is there a better way to write this code so it goes faster? Right now it takes approx 2 minutes ...
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
try
Code:Range("L1:L3000").FormulaR1C1 = "=IF(Consolidation!R[7]C[-3]="""","""",Consolidation!R[7]C[-3])"
If the above suggestion contains vb code and you're not sure about using macros,check the following links (which in no way should be interpreted as being specific to your individual query):
http://www.mrexcel.com/articles/past...o-into-vbe.php
You can find help with array formulas here:
http://www.cpearson.com/excel/arrayformulas.aspx
If you really want to learn Excel, don't always accept the first solution.
Should L1:L3000 change to L1:T3000 ?
This runs in less than a second on my machine (XL 2007):
You're probably throttling the Calc Engine...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
Let it happen, Captain!
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])"
If the above suggestion contains vb code and you're not sure about using macros,check the following links (which in no way should be interpreted as being specific to your individual query):
http://www.mrexcel.com/articles/past...o-into-vbe.php
You can find help with array formulas here:
http://www.cpearson.com/excel/arrayformulas.aspx
If you really want to learn Excel, don't always accept the first solution.
WOW, thanks both of you. This is super speedy now.
You're welcome. It also occurs to me that changing this:
To this:Code:With Application .ScreenUpdating = True .Calculation = clcMode End With
Would be potentially faster, to recalc before you redraw the screen.Code:With Application .Calculation = clcMode .ScreenUpdating = True End With
Like this thread? Share it with others