This piece of code is very slow - please help refine

[kostas]

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.

For i = 1 To iterations
    Range("A" & (i + 10)).Formula = i
    Range("c" & (i + 10)).Formula = Worksheets(settingssheet).Cells(celllocation + i - 1, 10)

    datasheet = "D_" & Worksheets(settingssheet).Cells(celllocation + i - 1, 10)
    
    Range("D" & (i + 10)).Select
    Selection.FormulaArray = "=STDEV(QUARTILE(IF(('" & datasheet & "'!$K$2:$K$65500>0),'" & datasheet & "'!$D$2:$D$65500),2),QUARTILE(IF(('" & datasheet & "'!$K$2:$K$65500>0),'" & datasheet & "'!$D$2:$D$65500),3))"
 
    Range("H" & (i + 10)).Select
    Selection.FormulaArray = "=COUNT(IF('" & datasheet & "'!K2:K65500>0,'" & datasheet & "'!K2:K65500))"

    Range("E" & (i + 10)).Formula = i * 0.0000000001
    
    Range("F" & (i + 10)).Formula = "=D" & (i + 10) & "+E" & (i + 10)
    
    Range("G" & (i + 10)).Formula = "=B" & (i + 10)
    
    Worksheets("league board").Range("b" & (i + 10)).Formula = "=Rank(F" & (i + 10) & ",$F$11:$F$" & (10 + iterations) & ", 1)"

    Range("I" & (i + 10)).Select
    Selection.FormulaArray = "=(AVERAGE(QUARTILE(IF(('" & datasheet & "'!$K$2:$K$65500>0),'" & datasheet & "'!$D$2:$D$65500),2),QUARTILE(IF(('" & datasheet & "'!$K$2:$K$65500>0),'" & datasheet & "'!$D$2:$D$65500),3)))/ (60 * 24)"
          
Next i

[mudraker]

I don't fully understaqnd what your code is doing

Ways to speed up your code

Avoid selecting cells, ranges etc

try replacing

Range("D" & (i + 10)).Select
    Selection.FormulaArray = "=STDEV(QUARTILE(IF(('" & datasheet & "'!$K$2:$K$65500>0),'" & datasheet & "'!$D$2:$D$65500),2),QUARTILE(IF(('" & datasheet & "'!$K$2:$K$65500>0),'" & datasheet & "'!$D$2:$D$65500),3))"

With

 Range("D" & (i + 10)).FormulaArray = "=STDEV(QUARTILE(IF(('" & datasheet & "'!$K$2:$K$65500>0),'" & datasheet & "'!$D$2:$D$65500),2),QUARTILE(IF(('" & datasheet & "'!$K$2:$K$65500>0),'" & datasheet & "'!$D$2:$D$65500),3))"

Loop through only used rows

'find last used row in column A
'change column letter to suit
lLR = Cells(Rows.Count, "a").End(xlUp).Row
For iLoop = 1 to lLR
 your code here
Next iLoop

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

[Cyclops]

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

Range("A" & (i + 10)).Formula = i

Could be replaced with something like.

Range("A11").Value = 1
Range("A12").Value = 2
Range("A11:A12").AutoFill Destination:=Range("A13:A65000"), Type:=xlFillDefault

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.

Range("D" & (i + 10)).

Could be replaced with

Range("D:D").cells(I+10).

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

dim RowCount as long
rowcount = activesheet.usedrange.rows.count
for i = 10 to rowcount + 10
...

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.

[kostas]

Avoid selecting cells, ranges etc
try replacing

Range("D" & (i + 10)).Select
    Selection.FormulaArray = "=STDEV(QUARTILE(IF(('" & datasheet & "'!$K$2:$K$65500>0),'" & datasheet & "'!$D$2:$D$65500),2),QUARTILE(IF(('" & datasheet & "'!$K$2:$K$65500>0),'" & datasheet & "'!$D$2:$D$65500),3))"

With

 Range("D" & (i + 10)).FormulaArray = "=STDEV(QUARTILE(IF(('" & datasheet & "'!$K$2:$K$65500>0),'" & datasheet & "'!$D$2:$D$65500),2),QUARTILE(IF(('" & datasheet & "'!$K$2:$K$65500>0),'" & datasheet & "'!$D$2:$D$65500),3))"

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

Loop through only used rows

'find last used row in column A
'change column letter to suit
lLR = Cells(Rows.Count, "a").End(xlUp).Row
For iLoop = 1 to lLR
 your code here
Next iLoop

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

[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)

Dim lShR As Long

lShR = Sheets(datasheet).Cells(Rows.Count, "k").End(xlUp).Row
Range("D" & (i + 10)).FormulaArray = "=STDEV(QUARTILE(IF(('" & datasheet & "'!$K$2:$K$" & lShR & "0>0),'" & datasheet & "'!$D$2:$D$" & lShR & "),2),QUARTILE(IF(('" & datasheet & "'!$K$2:$K$" & lShR & ">0),'" & datasheet & "'!$D$2:$D$" & lShR & "),3))"

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

[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)

Dim lShR As Long

lShR = Sheets(datasheet).Cells(Rows.Count, "k").End(xlUp).Row
Range("D" & (i + 10)).FormulaArray = "=STDEV(QUARTILE(IF(('" & datasheet & "'!$K$2:$K$" & lShR & "0>0),'" & datasheet & "'!$D$2:$D$" & lShR & "),2),QUARTILE(IF(('" & datasheet & "'!$K$2:$K$" & lShR & ">0),'" & datasheet & "'!$D$2:$D$" & lShR & "),3))"

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