 # 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 last used column/row in formulas?

5 Replies

# Re: Return the address of last cell by formulas

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

# Re: Return the address of last cell by formulas

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?

# Re: Return the address of last cell by formulas

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

# Re: Return the address of last cell by formulas

=MAX((A1:A1000480<>"")*ROW(1:1000480))

=MAX((1:1<>"")*TRANSPOSE(ROW(1:16384)))

Do you want formulas like these? See attached file.

# Re: Return the address of last cell by formulas

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.