Return the address of last cell by formulas

[qazzzlyt]

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?

[#2]

@qazzzlyt

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)))

[#3]

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?

[#4]

@qazzzlyt 

You could create a custom function in VBA, but you don't want to use that...

[#5]

@qazzzlyt 

=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.

[#6]

@qazzzlyt 

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.