You could use special cells xllastcell to get the last cell, or if you
can be certain that you don't have blanks in your data you could use
the COUNTA function to count the entries - which gives you the
necessary numeric part of the reference. You could even create a
dynamic named range and reference this.