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

Remove leading & trailing spaces
Save as CSV to remove all leading and trailing spaces. It is faster than using TRIM().

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
 

Watch MrExcel Video

Forum statistics

Threads
1,130,448
Messages
5,642,218
Members
417,262
Latest member
andrewd1

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