| Home |
| Search |
| Today's Posts |
|
#1
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
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 |
|
#2
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
|
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 |
|
#3
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
|
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 |
|
#4
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
|
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 |
|
#5
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
|
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 |
|
#6
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
|
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 |
|
#7
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
|
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 |
|
#8
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
|
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 |
| Reply |
| Thread Tools | Search this Thread |
| Display Modes | |
|
|
Similar Threads
|
||||
| Thread | Forum | |||
| Cell References | Excel Discussion (Misc queries) | |||
| How do I get a number or letter to represent cell contents? | Excel Discussion (Misc queries) | |||
| Urgent date/scheduling calc needed | Excel Worksheet Functions | |||
| Conversion of Cell Contents into a Functional Worksheet name ? | Excel Worksheet Functions | |||
| Function syntax to compare cell contents | Excel Worksheet Functions | |||
Linear Mode
