How to name a range that's flexible?

[hoopeht]

If you need to name a range in a spreadsheet, you would normally highlight the cells and type a name in the name box on the top left corner. But what do I do if the range needs to be flexible?

In cell A1, the user specifies an integer, say 10. I want B1:B10 to be named "user specified range". If the user specifies a different integer in A1, say 5, then B1:B5 would be named "user specified range".

How do I write a sub to make this happen?

 

[mikerickson]

You don't need a sub, you can do that with Names. Go into the Name Manager and define a name.

Name: UserSpecifiedRange
RefersTo: =Sheet1!$B$1:INDEX(Sheet1!$B:$B, Sheet1!$A$1, 1)