Is it possible to emulate the current region property using a function? I
can't use VBA and need to get the number of rows from a starting cell till
the next blank row down.

Try one of these in say, B1, normal ENTER to confirm:
=MATCH(TRUE,INDEX(A1:A1000="",),0)-1
=MATCH(TRUE,INDEX(ISBLANK(A1:A1000),),0)-1

MATCH bit will return the row number of the 1st "blank" cell down in
A1:A100, the "-1" adjusts it to the number of filled rows from A1 down

The 1st expression traps both real blanks and formula returned blanks, while
the 2nd traps only real blank cells. Any good? hit the YES below
--
Max
Singapore
http://savefile.com/projects/236895
Downloads:27,000 Files:200 Subscribers:70
xdemechanik
---
"daverau" wrote:
Is it possible to emulate the current region property using a function? I
can't use VBA and need to get the number of rows from a starting cell till
the next blank row down.

Typo, it should have read as "A1:A1000" in this line:
MATCH bit will return the row number of the 1st "blank" cell down in
A1:A100, the "-1" adjusts it to the number of filled rows from A1 down
--
Max
Singapore
http://savefile.com/projects/236895
Downloads:27,000 Files:200 Subscribers:70
xdemechanik
---

Hi,

Try this

=MATCH(FALSE,INDEX(ISBLANK(range),,),0)-1

--
Regards,

Ashish Mathur
Microsoft Excel MVP
www.ashishmathur.com

"daverau" wrote in message
...
Is it possible to emulate the current region property using a function? I
can't use VBA and need to get the number of rows from a starting cell till
the next blank row down.

Thanks for the response Ashish, but I get 0 as the answer. When I change it
to TRUE rather than FALSE, the correct expected number appears.

Much appreciated.

Daverau

"Ashish Mathur" wrote:

Hi,

Try this

=MATCH(FALSE,INDEX(ISBLANK(range),,),0)-1

--
Regards,

Ashish Mathur
Microsoft Excel MVP
www.ashishmathur.com

"daverau" wrote in message
...
Is it possible to emulate the current region property using a function? I
can't use VBA and need to get the number of rows from a starting cell till
the next blank row down.

Thanks Max, that was exactly what I needed.

Cheers
Daverau

"Max" wrote:

Typo, it should have read as "A1:A1000" in this line:
MATCH bit will return the row number of the 1st "blank" cell down in
A1:A100, the "-1" adjusts it to the number of filled rows from A1 down
--
Max
Singapore
http://savefile.com/projects/236895
Downloads:27,000 Files:200 Subscribers:70
xdemechanik
---