Weird speed difference when setting values in adjacent columns

[ttu604]

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?

 

[easy2understandexcel]

It is probably running calculations based on the changing cell value.
At the beginning of each code put:

Application.ScreenUpdating = False
Application.Calculation = xlManual

and at the end put:

Application.ScreenUpdating = True
Application.Calculation = xlAutomatic

See if it doesnt speed right up

 

[ttu604]

I tried right away and the speed problem solved. I'm so grateful. Thank you!

 

[easy2understandexcel]

Hey, im glad I could help!
Just to bore you with the details...
It sounds like every time that cell is changing, excel is recalculating all of the formulas which that change impacts.
So on a large sheet, maybe that is forcing recalculations on millions of cells.

 

[ttu604]

Thank you for reminding. But before my testing, I cleaned all formulas, by converting every cell of the UsedRange into static value. Where the recalculations come from?

 

[easy2understandexcel]

I dont know, you tell me! Somewhere obviously if turning off calculations sped it up It could be other sheets.

 

[ttu604]

I see. I do have other sheets

 

[ttu604]

I still wonder why Code 1 is 200 times faster. Doesn't it cause recalculations?

 

[Michael M]

Maybe there is a larger range involved here.....and not just the used range
Consider that column 12 is affected by formulae in OTHER sheets , where column 13 isn't !!!

I cleaned all formulas, by converting every cell of the UsedRange

 

[ttu604]

According to your idea, I found a formula refer to column 12 in another sheet. After removed that formula, code 2 become as fast as code 1 was.

easy2understandexcel helped me solved the speed problem. Michael Mhelp me understand what exactly trigger the speed problem. This forum is really helpful.

Thank you all!