Weird speed difference when setting values in adjacent columns

ttu604

New Member
Joined
Dec 29, 2016
Messages
6
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?
 

Some videos you may like

Excel Facts

Format cells as time
Select range and press Ctrl+Shift+2 to format cells as time. (Shift 2 is the @ sign).

easy2understandexcel

Active Member
Joined
Dec 26, 2012
Messages
299
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

New Member
Joined
Dec 29, 2016
Messages
6
I tried right away and the speed problem solved. I'm so grateful. Thank you!
 

easy2understandexcel

Active Member
Joined
Dec 26, 2012
Messages
299
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.
 
Last edited:

ttu604

New Member
Joined
Dec 29, 2016
Messages
6

ADVERTISEMENT

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?
 

ttu604

New Member
Joined
Dec 29, 2016
Messages
6

ADVERTISEMENT

I see. I do have other sheets
 

ttu604

New Member
Joined
Dec 29, 2016
Messages
6
I still wonder why Code 1 is 200 times faster. Doesn't it cause recalculations?
 

Michael M

Well-known Member
Joined
Oct 27, 2005
Messages
19,766
Office Version
  1. 2016
  2. 2013
  3. 2007
Platform
  1. Windows
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 !!!

Code:
I cleaned all formulas, by converting every cell of the UsedRange
 
Last edited:

ttu604

New Member
Joined
Dec 29, 2016
Messages
6
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!
 

Watch MrExcel Video

Forum statistics

Threads
1,123,318
Messages
5,600,924
Members
414,416
Latest member
Nobu

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Top