try finding address of last non empty cell in a specific row(let say 3) [SOLVED]

[smittal]

can anyone please help out for same.


i want excel formula/VBA code to find out the address of last NON EMPTY CELL in a specific row. i had tried the below one but wrong result found. please HELP


=ADDRESS(ROW($A$4),MAX(NOT(ISBLANK($A4:$AH4))*COLUMN($A4:$AH4)))..... with


Ctrl+shift+enter


in attached reference , every last day of the week a column has been inserted. and i want to fetch the address of last "score2 cell" every time


https://www.dropbox.com/s/x4q79t6r40do8yc/testing.xlsm

 

[oldchippy]

Hi smittal,


Try this, it works for me


=ADDRESS(ROW($A$3),MAX(NOT(ISBLANK($A3:$AH3))*COLUMN($A3:$AH3))+1)


With Ctrl+Shift+Enter

 

[oldchippy]

Hi again,


Miss read your question first time, thought you were after the last blank cell, but your formula works


=ADDRESS(ROW($A$4),MAX(NOT(ISBLANK($A4:$AH4))*COLUMN($A4:$AH4)))


With Ctrl+Shift+Enter

 

[NARAYANK991]

Hi Mittal ,


Your formula works ; with the existing data , it returns the last non-empty cell ( the one which contains Score 2 ) as $U$4.


When I delete the data relating to Week 10 ( T3:U4 ) , it returns $S$4.


Narayan

 

[smittal]

i don't know... previously it was not working


Else.. thanks to all for this.. Can anyone please suggest what should i do if i don't want to stick formula with a specific column (AH) ..or we say that ...if data get extends from AH then..? what should be the formula for same..?


Please Help!!

 

[NARAYANK991]

Hi Mittal ,


Use any column which you are sure will never be used ; instead of column AH , use column ZZ.


Narayan

 

[smittal]

yes, i can but is there any other better solution for same for n number of length


can u please help...and one more... if in same sequence if score 3 also get added but we still require the last address of ("score 2 cell written") then what should we do..


Any Help will be highly appreciated

 

[NARAYANK991]

Hi Mittal ,


Use this :


=ADDRESS(ROW($A$3),MAX(NOT(ISBLANK(OFFSET($A3,,,,number_of_columns)))*COLUMN(OFFSET($A3,,,,number_of_columns))))


where number_of_columns can be any number from 2 upwards ( in your case , since column A is blank ).


Narayan

 

[smittal]

thanks Narayank... for help..can u please suggest on below mention point also..


if in same sequence if score 3 also get added but we still require the last address of ("score 2 cell written") then what should we do..

 

[NARAYANK991]

Hi Mittal ,


Try this :


=ADDRESS(ROW($A$4),MAX((OFFSET($A4,,,,number_of_columns)="Score 2")*COLUMN(OFFSET($A4,,,,number_of_columns))))


Narayan