Extremely slow run-time

[Steph]

Hello everybody. I have a seemingly strange problem that I'm hoping someone
can help with. I have a procedure (full code below) that clears cells on
multiple sheets and then copies a formula over the cleared range. The first
time I run the proc., it runs in seconds. The second time, it takes
forever! The third it takes seconds, the fourth it takes forever!
Literally, EVERY OTHER run takes forever! It seems so strange to me....To
be sure, I ran the code 20 times, and the pattern continues...every other
time it takes forever. I posted this question a little while ago without
much background or testing, and I got a few good answers like limit the
ranges that excel is copying to, and be sure the last cell is truly the last
cell of the used range, and turn off autocalc. I applied all suggestions,
with minimal difference.

When I step throgh the code, the parts that are slow are the ClearContents
loop and the PasteSpecial line.

Any ideas what causes a literal every-other-time slowdown?! Thanks in
advance!!!

-Steph


Public frng As Range

Sub Pop_Forecast()

Dim shtarray As Sheets
Dim frng As Range
Dim sh As Worksheet

Application.ScreenUpdating = False
Application.Calculation = xlCalculationManual
clrarray = Array("Forecast", "LMU", "Kit", "SMLC", "WLG", "SMLC Cab", "Serv
Cab", "Ntwk Kit", _
"TDAX", "EMS", "SCOUT", "Dir Coup")

For Each sh In ActiveWorkbook.Worksheets(clrarray)
sh.Range("A5:EC" & Data.Range("b65536").End(xlUp).Row).ClearContents
Next
Set shtarray = Sheets(Array("LMU", "Kit", "SMLC", "WLG", "SMLC Cab", "Serv
Cab", "Ntwk Kit", _
"TDAX", "EMS", "SCOUT", "Dir Coup"))

shtarray.Select
Sheets("LMU").Activate

'Copy Formula
Range("A2:EC2").Copy

Set frng = Range("A5:EC" & Data.Range("b65536").End(xlUp).Row)

frng.Select
Selection.PasteSpecial Paste:=xlPasteFormulas

Application.Calculation = xlCalculationAutomatic
'Range value detail sheets
' frng.Copy
' frng.PasteSpecial Paste:=xlValues
Application.Calculation = xlCalculationManual

'Consolidate2
Forecast.Range("A5").Consolidate Sources:=Array( _
"'LMU'!R5C5:R" & Format(LMU.Range("A65536").End(xlUp).Row) & "C133"
_
, "'Kit'!R5C5:R" & Format(LMU.Range("A65536").End(xlUp).Row) &
"C133" _
, "'SMLC'!R5C5:R" & Format(LMU.Range("A65536").End(xlUp).Row) &
"C133" _
, "'WLG'!R5C5:R" & Format(LMU.Range("A65536").End(xlUp).Row) &
"C133" _
, "'SMLC Cab'!R" & Format(LMU.Range("A65536").End(xlUp).Row) &
"C133" _
, "'Serv Cab'!R" & Format(LMU.Range("A65536").End(xlUp).Row) &
"C133" _
, "'Ntwk Kit'!R" & Format(LMU.Range("A65536").End(xlUp).Row) &
"C133" _
, "'TDAX'!R5C5:R" & Format(LMU.Range("A65536").End(xlUp).Row) &
"C133" _
, "'EMS'!R5C5:R" & Format(LMU.Range("A65536").End(xlUp).Row) &
"C133" _
, "'SCOUT'!R5C5:R" & Format(LMU.Range("A65536").End(xlUp).Row) &
"C133" _
, "'Dir Coup'!R5C5:R" & Format(LMU.Range("A65536").End(xlUp).Row) &
"C133" _
), Function:=xlSum, TopRow:=False, LeftColumn:=True,
CreateLinks:=False

Forecast.Activate
Application.Calculation = xlCalculationAutomatic

'Clear variables

Set shtarray = Nothing
Set clrarray = Nothing
Set sh = Nothing
Set frng = Nothing

Application.ScreenUpdating = True

End Sub

 

[keepITcool]

also note: I often set .enablecalculation to false on certain worksheets

also note: dont turn application.calculation on and off in the middle of
your proc

just calculate the sheet or range that needs to be calculated.
(instead of calculating all worksheets in the application instance)

also note:
range(a2:ec30000).Filldown
is more efficient then
range(x).copy
range(x).pasteformulas
be sure to test that range.rows.count>1 before using filldown!!


I saved the best for last:

I use following procedure to convert formulas to values on large ranges
it is lots and lots faster than copy/pastevalues







Sub Fml2Val(rng As Range)
Dim r As Long
Dim c As Integer
Dim bolMode As Boolean


With rng

bolMode = .Worksheet.EnableCalculation

If .Cells.Count <= 5000 Then
If Not bolMode Then .Worksheet.EnableCalculation = True
If Not a.Calculation = xlCalculationAutomatic Then .Calculate
If Not bolMode Then .Worksheet.EnableCalculation = bolMode
.Formula = .Value

Else

For r = 1 To .Rows.Count Step 1000

With .Rows(r).Resize(a.Min(.Rows.Count - r + 1, 1000))
For c = 1 To .Columns.Count
If Not bolMode Then .Worksheet.EnableCalculation =
True
If Not a.Calculation = xlCalculationAutomatic Then
..Columns(c).Calculate
If Not bolMode Then .Worksheet.EnableCalculation =
bolMode

.Columns(c).Formula = .Columns(c).Value
Next
End With
Next

End If

End With

End Sub





keepITcool

< email : keepitcool chello nl (with @ and .) >
< homepage: http://members.chello.nl/keepitcool >

 

[keepITcool]

oops.. replace a. with application.

(or set an a object, which is what I always do)

keepITcool

< email : keepitcool chello nl (with @ and .) >
< homepage: http://members.chello.nl/keepitcool >

 

[Steph]

Thanks keepITcool. I'll give it a shot. Do you have any idea as to why the
run-time varies so wodely with every other run? It is SO wierd! 7 seconds
one time, 7 minutes the next....and the pattern continues perfectly! I
can't help but think there is something strange there?! Thanks again.

 

[Steph]

Hi keepITcool,

I also tried the filldown rather than the pastespecial formulas, and the
filldown actually took longer!

 

[Charles Williams]

Hi keepitcool,

Worth noting that if calculation is automatic toggling .enablecalculation to
true will force a full calculation of every formula on the sheet, so its not
a good idea to embed this inside a column/row loop.

Charles
______________________
Decision Models
The Excel Calculation Site.
www.DecisionModels.com

 

[keepITcool]

Charles,

so noted!

I copied and adapted this from a file where I'm absolutely sure it's off.
(done in another routine)

I'll adapt it anyway.


keepITcool

< email : keepitcool chello nl (with @ and .) >
< homepage: http://members.chello.nl/keepitcool >