Roy_Excel_Island_Apps
Board Regular
- Joined
- Oct 9, 2018
- Messages
- 76
- Office Version
- 365
- Platform
- Windows
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
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