This piece of code is very slow - please help refine

I got the following loop that is very slow. The main way (I think) to speed it up is to avoid looking at all the 65000 cells. The columns used in the calculations have data varying from a few hundred cells up to (rarely) dozens of thousands. I assume it would be better if I could replace the 65500 with something indicating the "end" of the data in the column.

Also, is there another way to do this process without using a loop?

I am trying to fill in a number of rows with array formula's in the adjucent cells. I use the loop because the number of rows depends on a selection of another parameter which sets the row number before calling the loop.

I'll be happy to hear your ideas.

Please Login or Register  to view this content.

I don't fully understaqnd what your code is doing

Ways to speed up your code

Avoid selecting cells, ranges etc

try replacing

Please Login or Register  to view this content.

With

Please Login or Register  to view this content.

Loop through only used rows

Please Login or Register  to view this content.

Turn off Autocalculate & Screen Updates at the start of the macro

Turn them back on at the end of the macro

Set a variable with the value of i + 10 & replace all the i + 10 with the variable

It looks like some of the code could be replaced with autofill.

Please Login or Register  to view this content.

Could be replaced with something like.

Please Login or Register  to view this content.

You probably wouldn't want to fill the whole worksheet though. Determine used range as noted by mudraker.

This may work for other items too. I didn't really read closely enough to understand exactly what you're doing.


Also, I suspect that you could speed things up if you change the way you reference ranges.

Please Login or Register  to view this content.

Could be replaced with

Please Login or Register  to view this content.

You may also want to change the loop to start at 10 so you don't have to add 10 to I.

Please Login or Register  to view this content.

Be careful with .usedrange, if there is any formatting done to areas of the worksheet, excel will include this in the used range. Even if they don't contain any data. Mudraker's method for determining last used row will be more accurate. I usually use .usedrange because it's easier to type in.


Actually, after a quick test, it doesn't look like changing Range("D" & I +10) will save any time.


I'm not really sure but I'm under the impression that it would be quicker to store all the information in an array then write it to the worksheet in a single step. This may complicate things a bit though.

Originally Posted by mudraker

Avoid selecting cells, ranges etc
try replacing

Please Login or Register  to view this content.

With

Please Login or Register  to view this content.

thank you for this tip, I have done this change now.

Loop through only used rows

Please Login or Register  to view this content.

I am not quite sure how to do this, I shall explain the mechanism behind my code and perhaps you can see what I am trying to do clearer.

[quote[
Turn off Autocalculate & Screen Updates at the start of the macro

Turn them back on at the end of the macro
[/QUOTE]
Had done these already.

Ok this is what I am trying to do.

I have a set of worksheets which is determined by a selection before running the macro. The user can select one item out of ten, and depending on which one the user selects the number of produced worksheets change. Therefore I have linked "iterations" with the number of worksheets created. The "celllocation" bit just looks at a particular range of worksheet names to identify the right worksheet for the formula.

Next I need to run a series of array formulas on the columns of these worksheets and create a table. That is where the piece of code I wrote comes in.

So the "i=1 to iterations" bit essentialy determines the number of times I have to repeat this set of formulas for each worksheet, based on the number of worksheets (iterations). So if iterations=30 I have to repeat this set of formulas that follow 30 times, once for each worksheet.

The "range" bits I believe you understand, they just select a specific column from each worksheet and run an array formula. One of my problems is that because I don't know how many rows of data the produced worksheet will have (it cannot be estimated) I use the K2:K655500 instead of a more specific range. I believe that this delays the macro execution.

Other than that the rest of the macro is just calculations. All the "i+1" etc are there to work out the number of times needed to repeat the formulas or find the right worksheet etc. Considering that I might have 30-80 worksheets with a few thousand rows - and I have to apply all these quartile calculations on many of their columns definitely delays things but I am looking for anything that might help me speed up the process (unfortunately I can't get a new pc!).

As for your suggestions mudraker I am afraid that the first one, changing i with specific cell locations, will not work as the number of times I have to repeat the formulas is dynamic, so I don't know beforehand the range. I will look into your other suggestion of changing the usedrange to start automatically at 10 but do you have any estimate on the time saving? To give you an idea, at the moment it takes over an hour to run the macro so I'm looking for substantial savings. I am not trying to shave seconds.

I appreciate your help very much and thank you for taking time to reply to my request.

Regards,

Kostas

Is it possible to add the formulas after the sheets are populated with the data then you could use something like

(I'm assuming datasheet is the name of a sheet)

Please Login or Register  to view this content.

The size of the formula ranges will certainly slow things down & aslo cause slow recalcs

Originally Posted by mudraker

Is it possible to add the formulas after the sheets are populated with the data then you could use something like

(I'm assuming datasheet is the name of a sheet)

Please Login or Register  to view this content.

The size of the formula ranges will certainly slow things down & aslo cause slow recalcs

Thank you for this mudraker, I am testing it now and it seems to have sped up things considerably!

Regards,

Kostas