Finding row # of last cell containing contents
 

I have a 1-column (A) worksheet. Rows 1-5 may contain alphanumeric text or
be blank. Rows 6 thru ?? always contain alphanumeric text (i.e. no blank
cells). Starting with row 6, I need to determine the last row containing
alphanumeric text. I would prefer to use Excel's built-in functions rather
than a UDF.
Can anyone tell me how to do this?
Thanks,
Bob

Finding row # of last cell containing contents
 

Try this ARRAY FORMULA:

=MATCH(2,1/(1-ISBLANK(D6:D65536)))+5

Note: For array formulas, hold down [Ctrl] and [Shift] when you press
[Enter], instead of just pressing [Enter].


OR....if you only want the value of the last non-blank cell
=LOOKUP(2,1/(D6:D65536<""),D6:D65536)

Does that help?
***********
Regards,
Ron

XL2002, WinXP


"Bob" wrote:

I have a 1-column (A) worksheet. Rows 1-5 may contain alphanumeric text or
be blank. Rows 6 thru ?? always contain alphanumeric text (i.e. no blank
cells). Starting with row 6, I need to determine the last row containing
alphanumeric text. I would prefer to use Excel's built-in functions rather
than a UDF.
Can anyone tell me how to do this?
Thanks,
Bob

Finding row # of last cell containing contents
 

Typos (sort of)....

The formulas should reference Col_A, not Col_D

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

XL2002, WinXP


"Ron Coderre" wrote:

Try this ARRAY FORMULA:

=MATCH(2,1/(1-ISBLANK(D6:D65536)))+5

Note: For array formulas, hold down [Ctrl] and [Shift] when you press
[Enter], instead of just pressing [Enter].


OR....if you only want the value of the last non-blank cell
=LOOKUP(2,1/(D6:D65536<""),D6:D65536)

Does that help?
***********
Regards,
Ron

XL2002, WinXP


"Bob" wrote:

I have a 1-column (A) worksheet. Rows 1-5 may contain alphanumeric text or
be blank. Rows 6 thru ?? always contain alphanumeric text (i.e. no blank
cells). Starting with row 6, I need to determine the last row containing
alphanumeric text. I would prefer to use Excel's built-in functions rather
than a UDF.
Can anyone tell me how to do this?
Thanks,
Bob

Finding row # of last cell containing contents
 

Ron,
As always, thanks for your help!
Regards,
Bob


"Ron Coderre" wrote:

Typos (sort of)....

The formulas should reference Col_A, not Col_D

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

XL2002, WinXP


"Ron Coderre" wrote:

Try this ARRAY FORMULA:

=MATCH(2,1/(1-ISBLANK(D6:D65536)))+5

Note: For array formulas, hold down [Ctrl] and [Shift] when you press
[Enter], instead of just pressing [Enter].


OR....if you only want the value of the last non-blank cell
=LOOKUP(2,1/(D6:D65536<""),D6:D65536)

Does that help?
***********
Regards,
Ron

XL2002, WinXP


"Bob" wrote:

I have a 1-column (A) worksheet. Rows 1-5 may contain alphanumeric text or
be blank. Rows 6 thru ?? always contain alphanumeric text (i.e. no blank
cells). Starting with row 6, I need to determine the last row containing
alphanumeric text. I would prefer to use Excel's built-in functions rather
than a UDF.
Can anyone tell me how to do this?
Thanks,
Bob

Finding row # of last cell containing contents
 

Ron,
A follow-up question:

Can you tell me how I can incorporate your formula into a formula such as
=DCOUNTA(Summary!$A$5:$BX$zzz,Summary!$A$5,X!$O$3: $Q$4) where "zzz" is the
row number dervied from your formula?

Thanks again,
Regards,
Bob


"Ron Coderre" wrote:

Typos (sort of)....

The formulas should reference Col_A, not Col_D

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

XL2002, WinXP


"Ron Coderre" wrote:

Try this ARRAY FORMULA:

=MATCH(2,1/(1-ISBLANK(D6:D65536)))+5

Note: For array formulas, hold down [Ctrl] and [Shift] when you press
[Enter], instead of just pressing [Enter].


OR....if you only want the value of the last non-blank cell
=LOOKUP(2,1/(D6:D65536<""),D6:D65536)

Does that help?
***********
Regards,
Ron

XL2002, WinXP


"Bob" wrote:

I have a 1-column (A) worksheet. Rows 1-5 may contain alphanumeric text or
be blank. Rows 6 thru ?? always contain alphanumeric text (i.e. no blank
cells). Starting with row 6, I need to determine the last row containing
alphanumeric text. I would prefer to use Excel's built-in functions rather
than a UDF.
Can anyone tell me how to do this?
Thanks,
Bob

Finding row # of last cell containing contents
 

I didn't test this, but this is the form you'd need:

=DCOUNTA(INDIRECT("Summary!$A$5:$BX$"&(MATCH(2,1/(1-ISBLANK(D6:D65536)))+5)),Summary!$A$5,X!$O$3:$Q$4)

The INDIRECT function translates its contents into a valid Excel reference
(if possible).


Does that help?
***********
Regards,
Ron

XL2002, WinXP


"Bob" wrote:

Ron,
A follow-up question:

Can you tell me how I can incorporate your formula into a formula such as
=DCOUNTA(Summary!$A$5:$BX$zzz,Summary!$A$5,X!$O$3: $Q$4) where "zzz" is the
row number dervied from your formula?

Thanks again,
Regards,
Bob


"Ron Coderre" wrote:

Typos (sort of)....

The formulas should reference Col_A, not Col_D

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

XL2002, WinXP


"Ron Coderre" wrote:

Try this ARRAY FORMULA:

=MATCH(2,1/(1-ISBLANK(D6:D65536)))+5

Note: For array formulas, hold down [Ctrl] and [Shift] when you press
[Enter], instead of just pressing [Enter].


OR....if you only want the value of the last non-blank cell
=LOOKUP(2,1/(D6:D65536<""),D6:D65536)

Does that help?
***********
Regards,
Ron

XL2002, WinXP


"Bob" wrote:

I have a 1-column (A) worksheet. Rows 1-5 may contain alphanumeric text or
be blank. Rows 6 thru ?? always contain alphanumeric text (i.e. no blank
cells). Starting with row 6, I need to determine the last row containing
alphanumeric text. I would prefer to use Excel's built-in functions rather
than a UDF.
Can anyone tell me how to do this?
Thanks,
Bob

Finding row # of last cell containing contents
 

Ron,
That did the trick! Thanks a million.
Regards,
Bob


"Ron Coderre" wrote:

I didn't test this, but this is the form you'd need:

=DCOUNTA(INDIRECT("Summary!$A$5:$BX$"&(MATCH(2,1/(1-ISBLANK(D6:D65536)))+5)),Summary!$A$5,X!$O$3:$Q$4)

The INDIRECT function translates its contents into a valid Excel reference
(if possible).


Does that help?
***********
Regards,
Ron

XL2002, WinXP


"Bob" wrote:

Ron,
A follow-up question:

Can you tell me how I can incorporate your formula into a formula such as
=DCOUNTA(Summary!$A$5:$BX$zzz,Summary!$A$5,X!$O$3: $Q$4) where "zzz" is the
row number dervied from your formula?

Thanks again,
Regards,
Bob


"Ron Coderre" wrote:

Typos (sort of)....

The formulas should reference Col_A, not Col_D

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

XL2002, WinXP


"Ron Coderre" wrote:

Try this ARRAY FORMULA:

=MATCH(2,1/(1-ISBLANK(D6:D65536)))+5

Note: For array formulas, hold down [Ctrl] and [Shift] when you press
[Enter], instead of just pressing [Enter].


OR....if you only want the value of the last non-blank cell
=LOOKUP(2,1/(D6:D65536<""),D6:D65536)

Does that help?
***********
Regards,
Ron

XL2002, WinXP


"Bob" wrote:

I have a 1-column (A) worksheet. Rows 1-5 may contain alphanumeric text or
be blank. Rows 6 thru ?? always contain alphanumeric text (i.e. no blank
cells). Starting with row 6, I need to determine the last row containing
alphanumeric text. I would prefer to use Excel's built-in functions rather
than a UDF.
Can anyone tell me how to do this?
Thanks,
Bob

Finding row # of last cell containing contents
 

You're very welcome, Bob.......thanks for the feedback.

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

XL2002, WinXP


"Bob" wrote:

Ron,
That did the trick! Thanks a million.
Regards,
Bob


"Ron Coderre" wrote:

I didn't test this, but this is the form you'd need:

=DCOUNTA(INDIRECT("Summary!$A$5:$BX$"&(MATCH(2,1/(1-ISBLANK(D6:D65536)))+5)),Summary!$A$5,X!$O$3:$Q$4)

The INDIRECT function translates its contents into a valid Excel reference
(if possible).


Does that help?
***********
Regards,
Ron

XL2002, WinXP


"Bob" wrote:

Ron,
A follow-up question:

Can you tell me how I can incorporate your formula into a formula such as
=DCOUNTA(Summary!$A$5:$BX$zzz,Summary!$A$5,X!$O$3: $Q$4) where "zzz" is the
row number dervied from your formula?

Thanks again,
Regards,
Bob


"Ron Coderre" wrote:

Typos (sort of)....

The formulas should reference Col_A, not Col_D

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

XL2002, WinXP


"Ron Coderre" wrote:

Try this ARRAY FORMULA:

=MATCH(2,1/(1-ISBLANK(D6:D65536)))+5

Note: For array formulas, hold down [Ctrl] and [Shift] when you press
[Enter], instead of just pressing [Enter].


OR....if you only want the value of the last non-blank cell
=LOOKUP(2,1/(D6:D65536<""),D6:D65536)

Does that help?
***********
Regards,
Ron

XL2002, WinXP


"Bob" wrote:

I have a 1-column (A) worksheet. Rows 1-5 may contain alphanumeric text or
be blank. Rows 6 thru ?? always contain alphanumeric text (i.e. no blank
cells). Starting with row 6, I need to determine the last row containing
alphanumeric text. I would prefer to use Excel's built-in functions rather
than a UDF.
Can anyone tell me how to do this?
Thanks,
Bob