[SOLVED] SUMPRODUCT with DYNAMIC range

Originally Posted by hrlngrv

The last nonblank row in col B would be given by =MATCH(1,INDEX(0/NOT(ISBLANK(B:B)),0)).

Originally Posted by BMV

. . .
- it's worse then use 100 000 in first formula because this operation will calculate 1 048 576 cells. and

=LOOKUP(2;1/(B:B<>"");ROW(B:B))

- much shorter quicker and with the same problem.

In a new workbook, enter

A1: 0
B1: x
A2: =CHOOSE(A1,MATCH(1,INDEX(0/NOT(ISBLANK(B:B)),0)),LOOKUP(1,0/(B:B<>""),ROW(B:B)),MATCH(1,INDEX(0/NOT(ISBLANK(prune(B:B))),0)),lastrow(B:B))

Insert a general VBA module, and enter this code.

Option Explicit


Function prune(rng As Range) As Range
  Set prune = Intersect(rng, rng.Parent.UsedRange)
End Function


Function lastrow(rng As Range) As Long
  If rng.Columns.Count = 1 Then
    Set rng = Intersect(rng, rng.Parent.UsedRange)
    Set rng = rng.Rows(rng.Rows.Count)
    If IsEmpty(rng.Value2) Then Set rng = rng.End(xlUp)
    lastrow = rng.Row
  End If
End Function


Sub prof()
  Const MAXITER As Long = 200
  Const MAXBUMP As Long = 20

  Dim dt As Date, j As Long, k As Long

  For j = 1 To 4
    '# following 2 statements ensure a clean slate for each expression
    [B2:B1048576].ClearContents
    ThisWorkbook.Save
    dt = Now
    [A1].Value2 = j
    For k = 1 To MAXITER
      [B1].Offset([A2].Value2 + Application.WorksheetFunction.RandBetween(1, MAXBUMP), 0).Value2 = "x"
    Next k
    Debug.Print j, Format(dt, "hh:mm:ss"), Format(Now, "hh:mm:ss"), Format((Now - dt) * 86400# / MAXITER, "0.0000000000"), [A2].Value2
  Next j

  [A1].Value2 = 0
  [B2:B1048576].ClearContents
  ThisWorkbook.Save

End Sub

Now run prof. On my system (a VM with not a lot of RAM) I get the following results in the Immediate window.

1	08:06:32	08:07:00	0.1399999982	2253
2	08:07:01	08:07:24	0.1150000017	2193
3	08:07:25	08:07:25	0.0000000000	2380
4	08:07:26	08:07:26	0.0000000000	2386
-	--------	--------	------------	----
3	08:07:50	08:08:00	0.0049999999	23279
4	08:08:01	08:08:07	0.0030000001	22815

3 and 4 each took less than a second for 200 iterations, so I increased MAXITER to 2000 and reran them. This does show that your lookup formula is about 18% faster than my match formula, but using a little VBA chops orders of magnitude off the calculation time. Note: <>"" isn't the same thing as blank. If the last cell with visible contents was B99 and there were IFERROR(...,"") formulas in B100:B200 all returning "", your lookup formula would return 99 while my match formula would return 200.