Forum Discussion
Return the address of last cell by formulas
So I just learned that Ctrl + End will bring you to last used cell in excel (which is very useful, right?). Is there a way to refer to that cell in formulas? No VBA allowed. Or can I refer to la...
Here are a few examples.
1) Let's say column H contains numbers.
The number in the last used cell of column H is
=LOOKUP(9.99999999999999E+307,H:H)
2) The row number of that cell is
=MATCH(9.99999999999999E+307,H:H)
3) Let's say row 10 contains text values.
The text in the last used cell of row 10 is
=LOOKUP(REPT("z",255),10:10)
4) The column number of that cell is
=MATCH(REPT("z",255),10:10)
5) Let's say column B contains a mixture of text and numbers.
The row number of the last used cell in column B is
=MAX(MATCH(9.99999999999999E+307,B:B),MATCH(REPT("z",255),B:B))
6) The value of that cell is
=INDEX(B:B,MAX(MATCH(9.99999999999999E+307,B:B),MATCH(REPT("z",255),B:B)))
- Thanks for help. However in this way, only one column can be processed in each formula. Is there a way to process entire sheet in a formula?
You could create a custom function in VBA, but you don't want to use that...
