When Speed Counts...

thereuare

Board Regular
Joined
May 28, 2002
Messages
232
The following sub places numbers in columnA from Row1 to Row3000, where BoxSize is assigned from an earlier InputBox. Is there a faster way to do this, it surprisingly takes longer than i think it should:

For Count = 1 To 3000
Range("A1").Select
ActiveCell.Offset(3000 - Count, 0) = x + BoxSize
x = x + BoxSize
Next Count


Also, which of the following lines of code would be executed faster, they both do the same thing:

If CheckValue <= (CurrentBoxValue - (Reversal * BoxSize)) Then OtherSub

OR

If CheckValue<=(CurrentBoxValue-ActiveCell.Offset(z,-Reversal).Value) Then OtherSub

I'm not 100% the second code even works, but you get an idea of the syntax i want to use. Would one of these be faster than the other? (ie- the 1st code actuals performs math functions, the second looks up the value in a cell).

Thanks for any insight.
 

Excel Facts

Select all contiguous cells
Pressing Ctrl+* (asterisk) will select the "current region" - all contiguous cells in all directions.

Richie(UK)

MrExcel MVP
Joined
May 17, 2002
Messages
3,329
Hi,

Have you tried:

Application.Calculation = xlCalculationManual
'Your code here.
Application.Calculation = xlCalculationAutomatic

HTH

EDIT Also consider Application.ScreenUpdating=False

_________________<font color="blue"> «««<font color="red">¤<font color="blue"><font size=+1>Richie</font><font color="red">¤<font color="blue"> »»»</font>

caffeine_sample.gif
</gif>
This message was edited by Richie(UK) on 2002-09-06 07:07
 

thereuare

Board Regular
Joined
May 28, 2002
Messages
232
Not familiar with either of those, could you give bried explanation of what it does and how it works?

Thanks.

Also, any idea of the bottom question, if one of those codes has faster/slower execution?
 

Richie(UK)

MrExcel MVP
Joined
May 17, 2002
Messages
3,329
Hi,

From the VBE Help files:
Screenupdating:
True if screen updating is turned on. Read/write Boolean.

Remarks

Turn screen updating off to speed up your macro code. You won't be able to see what the macro is doing, but it will run faster.
Remember to set the ScreenUpdating property back to True when your macro ends (older versions of Microsoft Excel automatically reset this property, but Microsoft Excel 97 does not).

And for calculation:
This basically stops Excel performing each and every calculation until the routine is finished (when you switch back to automatic). This is especially useful in large loops.

Give each one a try (or even both) and see if it helps.

_________________<font color="blue"> «««<font color="red">¤<font color="blue"><font size=+1>Richie</font><font color="red">¤<font color="blue"> »»»</font>

caffeine_sample.gif
</gif>
This message was edited by Richie(UK) on 2002-09-06 08:53
 

Tommy Bak

Active Member
Joined
Feb 25, 2002
Messages
288
one thing I know can make this faster is to eliminate the selection of A1

For Count = 1 To 3000
Range("A1").Offset(3000 - Count, 0) = x + BoxSize
x = x + BoxSize
Next Count


regards Tommy
 
Master Excel Bundle

Excel contains over 450 functions, with more added every year. That’s a huge number, so where should you start? Right here with this bundle.

Forum statistics

Threads
1,164,517
Messages
5,837,809
Members
430,516
Latest member
thaling

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