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)
--
John C


"Arlen" wrote:

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