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