Worksheet function that duplicates End(xlUp)?
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))
--
Biff
Microsoft Excel MVP
"John C" <johnc@stateofdenial wrote in message
...
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
|