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?

--
Amedee

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

Don't loop:

Range("D2").FormulaArray = "=MAX(IF(R2C1:R6C1=RC1,R2C2:R6C2,FALSE))"
Range("D2").AutoFill Destination:=Range("D2:D" & r), Type:=xlFillDefault


--

HTH,
Bernie
MS Excel MVP


"Amedee Van Gasse" wrote in message
...
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?

--
Amedee

Ooops, sorry:

Range("D2").FormulaArray = "=MAX(IF(R2C1:R" & r & "C1=RC1,R2C2:R" & r &
"C2,FALSE))"
Range("D2").AutoFill Destination:=Range("D2:D" & r), Type:=xlFillDefault


HTH,
Bernie
MS Excel MVP


"Bernie Deitrick" <deitbe @ consumer dot org wrote in message
...
Don't loop:

Range("D2").FormulaArray = "=MAX(IF(R2C1:R6C1=RC1,R2C2:R6C2,FALSE))"
Range("D2").AutoFill Destination:=Range("D2:D" & r), Type:=xlFillDefault


--

HTH,
Bernie
MS Excel MVP


"Amedee Van Gasse" wrote in message
...
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?

--
Amedee

On 1 apr, 14:45, "Bernie Deitrick" <deitbe @ consumer dot org wrote:
Don't loop:

Range("D2").FormulaArray = "=MAX(IF(R2C1:R6C1=RC1,R2C2:R6C2,FALSE))"
Range("D2").AutoFill Destination:=Range("D2:D" & r), Type:=xlFillDefault

Thanks!

The code is now:


r = shDAT.UsedRange.Rows.Count
shDAT.Range("D2").FormulaArray = "=MAX(IF(R2C1:R" & r &
"C1=RC1,R2C2:R" & r & "C2,FALSE))"
shDAT.Range("D2").AutoFill Destination:=shDAT.Range("D2:D" & r),
Type:=xlFillDefault


Execution of this part of the code is now a magnitude faster, and it
produces exactly the same result.

Calculation at the end of the code (when calculation is set to
xlCalculationAutomatic again) is of course still slow, but this is
expected.
One does not simply walk into 10K array formulas and expect it to be
fast. ;-)

An alternative approach that should be orders of magnitude faster
would be to sort the data ascending or descending on column 1 and
descending on column 2, then use this formula (its not an array
formula) in column D

shDAT.Range("D2").Formula="=IF($A2<$A1,$B2,$B1)"
shDAT.Range("D2").AutoFill Destination:=shDAT.Range("D2:D" & r),
Type:=xlFillDefault

Charles


The code is now:


r = shDAT.UsedRange.Rows.Count
shDAT.Range("D2").FormulaArray = "=MAX(IF(R2C1:R" & r &
"C1=RC1,R2C2:R" & r & "C2,FALSE))"
shDAT.Range("D2").AutoFill Destination:=shDAT.Range("D2:D" & r),
Type:=xlFillDefault


Execution of this part of the code is now a magnitude faster, and it
produces exactly the same result.

Calculation at the end of the code (when calculation is set to
xlCalculationAutomatic again) is of course still slow, but this is
expected.
One does not simply walk into 10K array formulas and expect it to be
fast. ;-)