The standard dynamic range uses a named range based on the COUNTA formulae:

=OFFSET($H$1,0,0,COUNT($H:$H),1)

However, this only works where there are no blanks in the data. If the data is purely numeric, and contains blank cells in the data then use a range such as:

=OFFSET($H$1,0,0,MATCH(1E+306,$H:$H,1),1)

If the data is purely text then we will need a named range such as:

=OFFSET($H$1,0,0,MATCH(“*”,$H:$H,-1),1)

And if we have a mixture of text, numbers and spaces:

=OFFSET(Sheet1!$H$1,0,0,MAX(MATCH(1E+306,Sheet1!$A:$A,1), MATCH(“*”,Sheet1!$H:$H,-1)),1)

Where we take the maximum of the two previous match functions.
If we need a range that expands for every day of this calendar year:

=OFFSET(Sheet1!$A$1,0,0,1+TODAY()-DATE(YEAR(TODAY()),1,1),1)