Is there a way to select the next to last non-empty cell in a row? Thank you.
C Cong Nguyen Oct 9, 2007 #1 Is there a way to select the next to last non-empty cell in a row? Thank you.
E EllenM Oct 9, 2007 #2 Cong, Try Ctrl+right arrow, let go of the Ctrl key and press left arrow. Hope this helps.
G Gary''s Student Oct 9, 2007 #4 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
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
D Domenic Oct 9, 2007 #5 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!
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!
C Cong Nguyen Oct 9, 2007 #6 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.
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.
C Cong Nguyen Oct 9, 2007 #7 Domenic, The formula for text gives the last non-empty data, not the previous. Thank you
D Domenic Oct 9, 2007 #8 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!
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!
C Cong Nguyen Oct 9, 2007 #9 Domenic, Thank you -- Cong Nguyen (e-mail address removed) Domenic said: 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! Click to expand...
Domenic, Thank you -- Cong Nguyen (e-mail address removed) Domenic said: 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! Click to expand...
