Arguably, one of the most powerful features in Excel is the ability to create dynamic ranges, which expand or contract as you add or delete data. You can create a dynamic range that consists of a single row by choosing Insert | Name | Define, then enter MYRANGE in the Names In Workbook field, then enter the following formula
=OFFSET(Sheet1!$B$5,0,0,1,COUNTA(Sheet1!$5:$5))
in the Refers To field, then finally click Add to save the reference. in addition, it's even possible to take this method a step further and make both the rows AND columns dynamic, with the following syntax
=OFFSET(Sheet1!$B$5,0,0,COUNTA(Sheet1!$B:$B),COUNTA(Sheet1!$5:$5))
where the range counts how many cell entries are in column B, and how many cell entries are in row 5, then expands or contracts the range automatically.
