Current region emulation

[daverau]

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.

 

[Max]

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

 

[Max]

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

 

[Ashish Mathur]

Hi,

Try this

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

--
Regards,

Ashish Mathur
Microsoft Excel MVP
www.ashishmathur.com

 

[daverau]

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

 

[daverau]

Thanks Max, that was exactly what I needed.

Cheers
Daverau