Worksheet function that duplicates End(xlUp)?

[Arlen]

Hello, Excelsperts!

I have a SUMPRODUCT function applied to a huge range, some 14000 rows big.
This makes calculations a tedious and timely chore. But really, I only need
that kind of size for one of my worksheets. The other sheets are all much
smaller.

Is there some way to say SUMPRODUCT($B$4 to Last row with data in B) rather
than $B$4:$B$14200? In a worksheet function, not a macro?

Thank you for your time and your brainpower.

Arlen

 

[John C]

One way you could represent the range of $b$2:$b$14000 with just the
'occupied' cells would be:
OFFSET($B$2,0,0,COUNTA($B$2:$B$14000),1)
This would mean that there is nothing in the blank cells, including not
having formulas. If you have formulas all the way down, you could consider
the following:
OFFSET($B$2,0,0,13999-COUNTBLANK($B$2:$B$14000),1)

 

[T. Valko]

OFFSET($B$2,0,0,COUNTA($B$2:$B$14000),1)

Using a volatile function will just make it worse.

This does the same thing and is not volatile:

B2:INDEX(B2:B14000,COUNTA(B2:B14000))