How to Find the Last Cell That Is Not Blank in A Table

5

5thQuadrant

New member
Joined
Mar 18, 2021
Messages
20
Reaction score
0
Points
0
Excel Version(s)
MS 365 for Enterprise
I need to find the last non-blank cell in a table and display its value in a cell.
Here is an example showing the last non-blank cell is "F5".
I did searches on the net and found solutions that finding the last non-blank cell in a column or row, not a table.

Your help is much appreciated.

Find the Last Non-Blank Cell.jpg
 
Which is the last non-blank cell in this diagram, B or C, and why?:
2021-03-18_224136.png
 
Which is the last non-blank cell in this diagram, B or C, and why?
Click to expand...

Good question.
The last cell should be defined by its column tag then row number. In the case of your example the last one would be the cell with value "B".
Thanks.
 
A formula such as:
Code: 
=INDEX(E2:N11,MAX(IF(ISBLANK(INDEX(E2:N11,0,MAX(IF(ISBLANK(E2:N11),FALSE,COLUMN(E2:N11)-COLUMN(E2)+1)))),FALSE,ROW(E2:N11)-ROW(E2)+1)),MAX(IF(ISBLANK(E2:N11),FALSE,COLUMN(E2:N11)-COLUMN(E2)+1)))
seems to me to be unduly long, but it's what first worked while I experimented. I suspect there'll be something a lot shorter.
What version of Excel are you using; I can try with some of the more recent worksheet formulae.
As it stands, this formula might need array-entering (means committing the formula to the sheet using Ctrl+Shift+Enter rather than just Enter).

There are 7 references to the whole table range (E2:N11) and 3 references to the top left cell of the table range (E2).
 
p45cal said:
A formula such as:
Code: 
=INDEX(E2:N11,MAX(IF(ISBLANK(INDEX(E2:N11,0,MAX(IF(ISBLANK(E2:N11),FALSE,COLUMN(E2:N11)-COLUMN(E2)+1)))),FALSE,ROW(E2:N11)-ROW(E2)+1)),MAX(IF(ISBLANK(E2:N11),FALSE,COLUMN(E2:N11)-COLUMN(E2)+1)))
seems to me to be unduly long, but it's what first worked while I experimented. I suspect there'll be something a lot shorter.
What version of Excel are you using; I can try with some of the more recent worksheet formulae.
As it stands, this formula might need array-entering (means committing the formula to the sheet using Ctrl+Shift+Enter rather than just Enter).

There are 7 references to the whole table range (E2:N11) and 3 references to the top left cell of the table range (E2).
Click to expand...

Great!
I will give it a try shortly.
I am with "365 enterprise".
Thanks. :smile:
 
Yep, it works supposedly. And it'd be great if the formula can be shorten.
I only have very basic skill in excel formula.
 
A bit shorter (if the formula's on the same sheet):
Code: 
=INDIRECT(ADDRESS(XLOOKUP(TRUE,INDEX(E2:N11,0,MAX((E2:N11<>"")*COLUMN(E2:N11))-COLUMN(E2)+1)<>"",ROW(E2:N11),"",0,-1),MAX((E2:N11<>"")*COLUMN(E2:N11))))
 
Could be shorter with:
Code: 
=LastinLastColumn(E2:N11)
but that needs to be supported by the UDF:
Code: 
Function LastinLastColumn(rng)
Set rr = rng.Find(What:="*", LookAt:=xlPart, LookIn:=xlFormulas, SearchOrder:=xlByColumns, SearchDirection:=xlPrevious, MatchCase:=False, Searchformat:=False)
If Not rr Is Nothing Then LastinLastColumn = rr.Value Else LastinLastColumn = "Empty range"
End Function
 
p45cal said:
A bit shorter (if the formula's on the same sheet):
Code: 
=INDIRECT(ADDRESS(XLOOKUP(TRUE,INDEX(E2:N11,0,MAX((E2:N11<>"")*COLUMN(E2:N11))-COLUMN(E2)+1)<>"",ROW(E2:N11),"",0,-1),MAX((E2:N11<>"")*COLUMN(E2:N11))))
Click to expand...

I tried but it returns an error "#NAME".
Yes, the table and the formula are on the the same sheet.
Any thought?
 
Which formula are you getting this error with?
If it's the one including XLOOKUP make sure it's right and no extra spaces have crept into it.
It would surprise me if XLOOKUP is the problem unless your O365 hasn't been updated in years.
If it's the LastinLastColumn one you need to have that code too (and if you're on a Mac you might need to remove the SearchFormat:= True bit.

Otherwise attach the workbook with this error.
 
Sorry, I was working on another machine with older version of excel. I did not realised.
It is no issue when the file is opened with 365.
Thanks again.
 
You must log in or register to reply here.
Back
Top