Is there a function that will return the number of rows between two cells of
unknown distance, similar to selecting the first cell and pressing
SHIFT+CRTL+DOWN KEY and the number of rows is displayed in the "Name Box" in
the upper left corner of the spread sheet?

Thanks

Dan

Try something like this:

=ROWS(A1:C10)

In that case, the function returns 10.

Does that help?

***********
Regards,
Ron

XL2002, WinXP-Pro


"Dan" wrote:

Is there a function that will return the number of rows between two cells of
unknown distance, similar to selecting the first cell and pressing
SHIFT+CRTL+DOWN KEY and the number of rows is displayed in the "Name Box" in
the upper left corner of the spread sheet?

Thanks

Dan

It would if I knew the end cell, i.e. "C10", but I do not. I have no idea
which row the information will be entered. The only cell I know is the
starting cell, i.e. A3. The next cell could be A5 or A16.

Thanks

"Ron Coderre" wrote:

Try something like this:

=ROWS(A1:C10)

In that case, the function returns 10.

Does that help?

***********
Regards,
Ron

XL2002, WinXP-Pro


"Dan" wrote:

Is there a function that will return the number of rows between two cells of
unknown distance, similar to selecting the first cell and pressing
SHIFT+CRTL+DOWN KEY and the number of rows is displayed in the "Name Box" in
the upper left corner of the spread sheet?

Thanks

Dan

Yup...I understand, now.

See if this comes closer:

For a list of items (or blanks) in A1:A100, this formula returns the number
of contiguous non-blank cells, beginning with A1:

B1: =MIN(ISBLANK(A1:A100)*ROW(A1:A100)+NOT(ISBLANK(A1: A100))*10^10)-ROW(A1)

If you wanted to start from A25, this is the form:
B1:
=MIN(ISBLANK(A25:A100)*ROW(A25:A100)+NOT(ISBLANK(A 25:A100))*10^10)-ROW(A25)


Does that help?

***********
Regards,
Ron

XL2002, WinXP-Pro


"Dan" wrote:

It would if I knew the end cell, i.e. "C10", but I do not. I have no idea
which row the information will be entered. The only cell I know is the
starting cell, i.e. A3. The next cell could be A5 or A16.

Thanks

"Ron Coderre" wrote:

Try something like this:

=ROWS(A1:C10)

In that case, the function returns 10.

Does that help?

***********
Regards,
Ron

XL2002, WinXP-Pro


"Dan" wrote:

Is there a function that will return the number of rows between two cells of
unknown distance, similar to selecting the first cell and pressing
SHIFT+CRTL+DOWN KEY and the number of rows is displayed in the "Name Box" in
the upper left corner of the spread sheet?

Thanks

Dan

Something is wrong with the formula. If I enter any value between A2 to A100
the answer is always "0". If I enter a value into A1 the answer is
"9999999999".

Trying to disect the equation to see if it works.

Thanks,

Dan

"Ron Coderre" wrote:

Yup...I understand, now.

See if this comes closer:

For a list of items (or blanks) in A1:A100, this formula returns the number
of contiguous non-blank cells, beginning with A1:

B1: =MIN(ISBLANK(A1:A100)*ROW(A1:A100)+NOT(ISBLANK(A1: A100))*10^10)-ROW(A1)

If you wanted to start from A25, this is the form:
B1:
=MIN(ISBLANK(A25:A100)*ROW(A25:A100)+NOT(ISBLANK(A 25:A100))*10^10)-ROW(A25)


Does that help?

***********
Regards,
Ron

XL2002, WinXP-Pro


"Dan" wrote:

It would if I knew the end cell, i.e. "C10", but I do not. I have no idea
which row the information will be entered. The only cell I know is the
starting cell, i.e. A3. The next cell could be A5 or A16.

Thanks

"Ron Coderre" wrote:

Try something like this:

=ROWS(A1:C10)

In that case, the function returns 10.

Does that help?

***********
Regards,
Ron

XL2002, WinXP-Pro


"Dan" wrote:

Is there a function that will return the number of rows between two cells of
unknown distance, similar to selecting the first cell and pressing
SHIFT+CRTL+DOWN KEY and the number of rows is displayed in the "Name Box" in
the upper left corner of the spread sheet?

Thanks

Dan

Oops! I apologize..

B1:
=MIN(ISBLANK(A25:A100)*ROW(A25:A100)+NOT(ISBLANK(A 25:A100))*10^10)-ROW(A25)

That's an array formula and must be commited by holding down the [Ctrl] and
[Shift] keys when you press [Enter].

***********
Regards,
Ron

XL2002, WinXP-Pro


"Dan" wrote:

Something is wrong with the formula. If I enter any value between A2 to A100
the answer is always "0". If I enter a value into A1 the answer is
"9999999999".

Trying to disect the equation to see if it works.

Thanks,

Dan

"Ron Coderre" wrote:

Yup...I understand, now.

See if this comes closer:

For a list of items (or blanks) in A1:A100, this formula returns the number
of contiguous non-blank cells, beginning with A1:

B1: =MIN(ISBLANK(A1:A100)*ROW(A1:A100)+NOT(ISBLANK(A1: A100))*10^10)-ROW(A1)

If you wanted to start from A25, this is the form:
B1:
=MIN(ISBLANK(A25:A100)*ROW(A25:A100)+NOT(ISBLANK(A 25:A100))*10^10)-ROW(A25)


Does that help?

***********
Regards,
Ron

XL2002, WinXP-Pro


"Dan" wrote:

It would if I knew the end cell, i.e. "C10", but I do not. I have no idea
which row the information will be entered. The only cell I know is the
starting cell, i.e. A3. The next cell could be A5 or A16.

Thanks

"Ron Coderre" wrote:

Try something like this:

=ROWS(A1:C10)

In that case, the function returns 10.

Does that help?

***********
Regards,
Ron

XL2002, WinXP-Pro


"Dan" wrote:

Is there a function that will return the number of rows between two cells of
unknown distance, similar to selecting the first cell and pressing
SHIFT+CRTL+DOWN KEY and the number of rows is displayed in the "Name Box" in
the upper left corner of the spread sheet?

Thanks

Dan