Nov 14 2021 12:22 AM
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 last used column/row in formulas?
Nov 14 2021 02:14 AM
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)))
Nov 14 2021 02:18 AM
Nov 14 2021 02:51 AM
You could create a custom function in VBA, but you don't want to use that...
Nov 14 2021 02:59 AM
=MAX((A1:A1000480<>"")*ROW(1:1000480))
=MAX((1:1<>"")*TRANSPOSE(ROW(1:16384)))
=ADDRESS(F5,F8)
Do you want formulas like these? See attached file.
Nov 14 2021 09:21 AM
I don't think that's possible, at least with formulae only. Let assume in new sheet we have data as
Ctrl+End forwards us on A10. Let delete values in few latest cells and press Ctrl+End again
It forwards us on A10. To Del on cells is not enough, we shall use Clear -> Clear All. Otherwise, especially within workbook with which you worked long enough, Ctrl+End forwards us on unpredictable position. More exactly on the last cells which ever had data.
Perhaps that's not what you are looking for and the task to take position of last non-blank cell. For one column that's easy
In other cases that's more complex, but could be possible. More logic is to be defined what we would like to receive. For example, here
Ctrl+End positions us on D10. But what we'd like to find, C8 or A6? And if in column A we have values till A11?
Bottom line, more concrete question for more concrete task is desirable.