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

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

Hope this helps.

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

--
Cong Nguyen



"Cong Nguyen" wrote:

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

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
--
Gary''s Student - gsnu200749


"Cong Nguyen" wrote:

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

If the data contains numerical values...

=LOOKUP(9.99999999999999E+307,A2:INDEX(A2:A100,MAT CH(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!

In article ,
Cong Nguyen wrote:

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

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.

--
Cong Nguyen



"Gary''s Student" wrote:

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
--
Gary''s Student - gsnu200749


"Cong Nguyen" wrote:

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

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



"Domenic" wrote:

If the data contains numerical values...

=LOOKUP(9.99999999999999E+307,A2:INDEX(A2:A100,MAT CH(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!

In article ,
Cong Nguyen wrote:

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

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!

In article ,
Cong Nguyen wrote:

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

Domenic,
Thank you
--
Cong Nguyen



"Domenic" wrote:

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!

In article ,
Cong Nguyen wrote:

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