Current region emulation
 

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.

Current region emulation
 

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.

Current region emulation
 

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
---

Current region emulation
 

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.

Current region emulation
 

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.

Current region emulation
 

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
---