| Home |
| Search |
| Today's Posts |
|
#10
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
How do i find last not empty cell in a range
Rick this is a wery nice job thanks alot
"Rick Rothstein" skrev: Not sure why you only want the address, especially if you are going to do another formula built around it (there is usually a better way for that scenario than creating the address); however, to answer your question, give this array-entered** formula a try... =ADDRESS(MAX((A1:F6000<"")*ROW(A1:A6000)),MAX(IF( INDIRECT("A"&MAX((A1:F6000<"")*ROW(A1:A6000))&":F "&MAX((A1:F6000<"")*ROW(A1:A6000)))<"",COLUMN(IN DIRECT("A"&MAX((A1:F6000<"")*ROW(A1:A6000))&":F"& MAX((A1:F6000<"")*ROW(A1:A6000))))))) **Commit formula using Ctrl+Shift+Enter and not just Enter by itself Note: Change all occurrences of 6000 to the maximum row number you ever expect to have data in. -- Rick (MVP - Excel) "excelent" wrote in message ... My senario (xl2003) B3,B4,B4 holds an X C5,C6,C8,C9,C10 holds an X E4,E5,E6,E7 holds an X and nothing more on that sheet Is there a formula that return's a cell-address C10 (last not empty) And how to do the same in VBA i no about selection.specialcell.xllast to find row 10 and now i do a loop thru rng A10 to F10 but is there another smart way ? |
| Thread Tools | Search this Thread |
| Display Modes | |
|
|
Similar Threads
|
||||
| Thread | Forum | |||
| Find empty cell then stop | Excel Discussion (Misc queries) | |||
| How to: Find first empty cell in column | Excel Worksheet Functions | |||
| find last none empty cell | Excel Discussion (Misc queries) | |||
| Find an empty cell and put a dash in it? | Excel Worksheet Functions | |||
| Find a empty cell in next column | Excel Discussion (Misc queries) | |||
Threaded Mode
