What do you mean by " end of the range" ?Only a columnar or row range ?
In this case the answer an be found at Lookup Last Value in Column or Row – Excel - Automate Excel (among others)

Use the INDEX formulas which return cell references when embedded like
or variations thereupon
Of course posting an example sheet would make our life easier ( no pics please)

__________________
Did you know you can thank someone who helped you? Click on the tiny scale in the right upper hand corner of your helper's post