On 1 apr, 14:14, Amedee Van Gasse wrote:
Excel 2007.

I found a bottleneck in my code, in this part:

* * * * * * r = shDAT.UsedRange.Rows.Count
* * * * * * For i = 2 To r
* * * * * * * * shDAT.Cells(i, 4).FormulaArray = _
* * * * * * * * * * "=MAX(IF(R2C1:R" & r & "C1=RC1,R2C2:R" & r &
"C2,FALSE))"
* * * * * * * * Application.StatusBar = Format(i / r, "0.0%") * * * '
Added to see the progress
* * * * * * Next

Currently the value of r is 13693.
Screenupdating & calculation are already disabled.

I know from literature that extensive use of array formulas can slow
down a sheet, and now I experienced it first hand.

What suggestions or strategies do you suggest to speed this up? I
suppose that a rewrite of the logic of this part is needed?

FYI, I also tried this but that gives a different array formula:

shDAT.Range(Cells(2, 4), Cells(r, 4)).FormulaArray = "=MAX(IF(R2C1:R"
& r & "C1=RC1,R2C2:R" & r & "C2,FALSE))"