# Weird speed difference when setting values in adjacent columns

#### ttu604

##### New Member
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?

### Excel Facts

How to calculate loan payments in Excel?
Use the PMT function: =PMT(5%/12,60,-25000) is for a \$25,000 loan, 5% annual interest, 60 month loan.
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

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

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:
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?

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

I see. I do have other sheets

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

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:
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!

Replies
0
Views
214
Replies
2
Views
844
Replies
2
Views
537
Replies
13
Views
410
Replies
5
Views
217

1,211,841
Messages
6,104,304
Members
447,901
Latest member
boy3hc2004

### 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.

### Which adblocker are you using?

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

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