HELP: Last row of the column containing blank cells between non-blank cells
How can I get the Last row of the column containing blank cells between
non-blank cells. Example: the data in a column is 1 (blank cell) 2 3 4 5 How can I get last row of this column? Thanks in advance for your replies. |
For data from A1:A10
------------------------- If you want the row number of the last data item: =SUMPRODUCT(MAX((A1:A10<"")*ROW(A1:A10))) If you want the value of the last data item (and your data starts at A1: =OFFSET(A4,SUMPRODUCT(MAX((A4:A10<"")*ROW(A4:A10) ))-1,0) For data from A4:A10 ------------------------- If you want the value of the last data item (and your data starts at a row other than A1: =OFFSET(A4,SUMPRODUCT(MAX((A4:A10<"")*ROW(A4:A10) ))-ROW(A4),0) Does that give you something to work with? -- Regards, Ron "Space Ape" wrote: How can I get the Last row of the column containing blank cells between non-blank cells. Example: the data in a column is 1 (blank cell) 2 3 4 5 How can I get last row of this column? Thanks in advance for your replies. |
Space Ape wrote:
How can I get the Last row of the column containing blank cells between non-blank cells. Example: the data in a column is 1 (blank cell) 2 3 4 5 How can I get last row of this column? Thanks in advance for your replies. The native row number wtr the last numeric value: =MATCH(9.99999999999999E+307,A:A) if the column of interest is A. To fetch the last numeric value itself: =LOOKUP(9.99999999999999E+307,A:A) |
All times are GMT +1. The time now is 12:41 AM. |
Powered by vBulletin® Copyright ©2000 - 2024, Jelsoft Enterprises Ltd.
ExcelBanter.com