My VBA macro process large worksheets (between 10~30 thousand rows). I notice big speed difference in 2 adjacent columns. To narrow the problem, I wrote 2 simple testing codes:
Code 1:
For i = 1 to 10000
Cells(i,13).Value = 1
Next
Code 2:
For i = 1 to 10000
Cells(i,12).Value = 1
Next
Code 1 took 1 second to complete, which is good. However, code 2 took more than 200 seconds to complete. I cleaned all formulas before testing.
Similar speed differences can be found in other columns
I have no idea where these differences come from, which is important for further optimzation. Can anybody help?
Code 1:
For i = 1 to 10000
Cells(i,13).Value = 1
Next
Code 2:
For i = 1 to 10000
Cells(i,12).Value = 1
Next
Code 1 took 1 second to complete, which is good. However, code 2 took more than 200 seconds to complete. I cleaned all formulas before testing.
Similar speed differences can be found in other columns
I have no idea where these differences come from, which is important for further optimzation. Can anybody help?