MrExcel Publishing
Your One Stop for Excel Tips & Solutions

Finding last non-null cell in a range


Posted by Mike Fleuette on August 03, 2001 11:26 AM

Is there an easy way to find the last non-null cell in a range? It is easy to find the first using MATCH()...


Posted by Mark W. on August 03, 2001 12:19 PM

For data in rows use....

{=MAX((A1:A10<>"")*ROW(A1:A10))}

...for data in columns use...

{=MAX((A1:J1<>"")*COLUMN(A1:J1))}


Note: These are an array formulas which must be
entered using the Control+Shift+Enter key combination.
The braces, {}, are not entered by you.

Posted by Mark W. on August 03, 2001 12:21 PM

Oops!! Make that...

For data in a column use....

{=MAX((A1:A10<>"")*ROW(A1:A10))}

...for data in a row use...

Note: These are an array formulas which must be
entered using the Control+Shift+Enter key combination.
The braces, {}, are not entered by you.

Yep, much better!! : )