Offset problem: Highlight range to the last filled cell (even with blanks in the middle of range).

[countryfan_nt]

Hello friends, Hope all is well!

Please help me with the below Offset formula.
I want the formula to highlight all the range to the last filled cell, even if there are blanks along the way.
if changing the formula all together no problem.

Thank you so much in advance!

 

[Fluff]

How about

=OFFSET(Sheet1!$A$1,,,LOOKUP(2,1/(Sheet1!$A$1:$A$1000<>""),ROW(Sheet1!$A$1:$A$1000)))