Next to last non-empty cell in row

[Guest]

Is there a way to select the next to last non-empty cell in a row?
Thank you.

 

[Guest]

Cong,
Try Ctrl+right arrow, let go of the Ctrl key and press left arrow.

Hope this helps.

 

[Guest]

Ellen,
Thanks for the tip, but I need a formula.

 

[Guest]

Lets say the last filled cell in row #9 is K9
and that J9 contains 5. Then

=WhereIsIt(9) will display $J$9 - the location
of the next to the last filled cell in row 9

=WhatsInIt(9) will display 5, the content of that cell.

Here is the code:

Function WhereIsIt(n As Long) As String
m = Cells(n, Columns.Count).End(xlToLeft).Column
WhereIsIt = Cells(n, m - 1).Address
End Function

Function WhatsInIt(n As Long) As Variant
m = Cells(n, Columns.Count).End(xlToLeft).Column
WhatsInIt = Cells(n, m - 1).Value
End Function

 

[Domenic]

If the data contains numerical values...

=LOOKUP(9.99999999999999E+307,A2:INDEX(A2:A100,MATCH(9.99999999999999E+30
7,A2:A100)-1))

If the data contains text values...

=LOOKUP(REPT("z",255),A2:INDEX(A2:A100,MATCH(REPT("z",255),A2:A100)-1))

Adjust the ranges accordingly.

Hope this helps!

 

[Guest]

Hi Gary"s Student,

Sorry about the not-clear question.

I want a cell in another tab shows the content of the next to last non-empty
cell of a row from a different tab, i.e., $J$9

Thank you.

 

[Guest]

Domenic,
The formula for text gives the last non-empty data, not the previous.
Thank you

 

[Domenic]

Does the data contain formula blanks ("") ? If so, try the following
formula instead...

=INDEX(A2:A100,LARGE(IF(A2:A100<>"",ROW(A2:A100)-ROW(A2)+1),2))

Note that the formula needs to be confirmed with CONTROL+SHIFT+ENTER,
not just ENTER.

Hope this helps!

 

[Guest]

Domenic,
Thank you
--
Cong Nguyen
(e-mail address removed)

Does the data contain formula blanks ("") ? If so, try the following
formula instead...

=INDEX(A2:A100,LARGE(IF(A2:A100<>"",ROW(A2:A100)-ROW(A2)+1),2))

Note that the formula needs to be confirmed with CONTROL+SHIFT+ENTER,
not just ENTER.

Hope this helps!