| Home |
| Search |
| Today's Posts |
|
|
|
#1
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
Address of Last Cell With Data
Is it possible to detect the address of the last cell with data in a
worksheet? (Without having to resort to VBA!) For instance, if I have data in column A, what formula would I use to detect the address of the last cell with data? Thanks. -- tb |
|
#2
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
|
Address of Last Cell With Data
Try this:
=CELL("address",INDEX(A1:A65535,MATCH(2,INDEX(1/(1-ISBLANK(A1:A65535)),0)))) Is that something you can work with? -------------------------- Regards, Ron Microsoft MVP (Excel) (XL2003, Win XP) "tb" wrote in message ... Is it possible to detect the address of the last cell with data in a worksheet? (Without having to resort to VBA!) For instance, if I have data in column A, what formula would I use to detect the address of the last cell with data? Thanks. -- tb |
|
#3
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
|
Address of Last Cell With Data
I should mention that the formula I posted will return
the cell address of the last non-blank cell (text OR numeric). Does that help? -------------------------- Regards, Ron Microsoft MVP (Excel) (XL2003, Win XP) "Ron Coderre" wrote in message ... Try this: =CELL("address",INDEX(A1:A65535,MATCH(2,INDEX(1/(1-ISBLANK(A1:A65535)),0)))) Is that something you can work with? -------------------------- Regards, Ron Microsoft MVP (Excel) (XL2003, Win XP) "tb" wrote in message ... Is it possible to detect the address of the last cell with data in a worksheet? (Without having to resort to VBA!) For instance, if I have data in column A, what formula would I use to detect the address of the last cell with data? Thanks. -- tb |
|
#4
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
|
Address of Last Cell With Data
=LOOKUP(2,1/(A1:A65535<""),A1:A65535) for any data
=ADDRESS(MATCH(99^99,A:A),1) for numeric data =LOOKUP(REPT("z",255),A:A) for text data Gord Dibben MS Excel MVP On Wed, 12 Dec 2007 16:42:43 -0800 (PST), tb wrote: Is it possible to detect the address of the last cell with data in a worksheet? (Without having to resort to VBA!) For instance, if I have data in column A, what formula would I use to detect the address of the last cell with data? Thanks. |
|
#5
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
|
Address of Last Cell With Data
On Wed, 12 Dec 2007 16:42:43 -0800 (PST), tb wrote:
Is it possible to detect the address of the last cell with data in a worksheet? (Without having to resort to VBA!) For instance, if I have data in column A, what formula would I use to detect the address of the last cell with data? Thanks. =ADDRESS(MATCH(2,1/NOT(ISBLANK(A1:A65535))),1) entered as an **array** formula (enter with <ctrl<shift<enter) --ron |
| Reply |
| Thread Tools | Search this Thread |
| Display Modes | |
|
|
Similar Threads
|
||||
| Thread | Forum | |||
| find last cell in range with data, display cell address | Excel Worksheet Functions | |||
| Identify address of cell containing specific data | Excel Discussion (Misc queries) | |||
| Data Validation: Store cell address instead of value in the cell? | Excel Discussion (Misc queries) | |||
| separate address data in a cell | Excel Discussion (Misc queries) | |||
| How do I include cell data in subject line of email address/hyperl | Excel Worksheet Functions | |||
Hybrid Mode
