VBA code running slow after specific code was executed

[Roy_Excel_Island_Apps]

Hi guys,

something weird is happening in my workbook code. I've been searching for hours what was slowing down the process:

1. I run my code when I've opened the workbook: 0 seconds to execute it.
2. I run my code a second time: Going 1 record per second (And I have 10K records...), so VERY SLOW
3. While running I break the execution and click 'End' so the macro stops.
4. I run it again: 0 seconds!
5. Step 2 happens again...
6. Loop..

I had a look at what is causing the code going slow at the second time you run it and this is the code:

' Set rng = shtOUTPUT.Range("A2:A" & LR)
' shtOUTPUT_Template.Range("H2").Resize(rng.Rows.Count, rng.Columns.Count).Cells.Value = rng.Cells.Value
'
' Set rng = shtOUTPUT.Range("B1:BE" & LR)
' shtOUTPUT_Template.Range("N1").Resize(rng.Rows.Count, rng.Columns.Count).Cells.Value = rng.Cells.Value

' Set rng = Nothing

When I put the code above in comments, the code is running FAST EVERY EXECUTION. So I am sure this code is slowing it down...BUT I DON'T KNOW WHY...
Can somebody tell me why??? Thanks in advance!

Regards, Roy

 

[jkpieterse]

Calculation set to manual in the fast case but to automatic in the slow case?

 

[Roy_Excel_Island_Apps]

Calculation set to manual in the fast case but to automatic in the slow case?

I set the calculation to manual in the beginning of the code... And where I need a calculation I use 'shtMasterGatdiepte.Rows(i + 2).Calculate'

 

[Fluff]

Does the value of LR change between the fast & slow runs?

 

[Roy_Excel_Island_Apps]

Does the value of LR change between the fast & slow runs?

LR = shtOUTPUT.Cells(shtOUTPUT.Rows.Count, "A").End(xlUp).Row

So yes. It counts the last row. This is always the same because I clear the sheet in the beginning of the execution: 'shtOUTPUT.Rows("2:" & Rows.Count).ClearContents'

 

[Fluff]

I clear the sheet in the beginning of the execution: 'shtOUTPUT.Rows("2:" & Rows.Count).ClearContents'

in that case there will be nothing to copy to the template sheet.