Enlarge range Name (Excel XP)

[BobWallToo]

<img src=/S/surrender.gif border=0 alt=surrender width=31 height=23> I have a range that extends from cell B2 to B5. It contains the values 1 through 4. I assigned the range name Bob to that range.

I add some more values, 5 through 9, at the bottom, from cell B6 through B10. I want to include these new cells in the range name Bob.

If I select the new range, and then I go to Insert Name Define, I can reenter Bob and I get the new range, covering from B2 to B10. This is cumbersome. If I make the slightest mistake, Bub, in that range name, it still works, but all my references to the range name Bob still refer to Bob and not to Bub.

Is there better way to enlarge a range name? Ideally, I would like to select the new range of cells and then select the range name, which would assign the new range to that range name.

[sdckapr]

Select Range
Insert - name -define
Enter the name <enter>

[As you notice, selecting or typing an existing range name, selects the range. This is by design it is not for editing ranges.]

If you are worried about mistyping the name:
Insert - name -define
Select the name
then in refers to
remove the name and then select a new range
<ok>

Steve

[Jezza]

Once more into the breach, dear friends, once more...

I always feel I am walking in the breach when I send a response after Steve but what the heck. Try this:

<pre>Sub NameChange()
Dim intRange As Integer

intRange = Range("A65535").End(xlUp) + 1
Range("A2:A" & intRange).Select

ActiveWorkbook.Names.Add Name:="Bob", RefersToR1C1:="=Sheet1!R2C1:R" & intRange & "C1"
End Sub</pre>


Obviously it will only work if there is only the named range "Bob" in it and no other data as it is counting the values in the column. I put a equation =max(Bob) in a vacant cell and added new data in the rows below and it changed. Maybe you could fiddle with this to get what you want.

[JohnBF]

Bob, this may not apply to your question, but if you are adding data to a (group of) Column(s), you could use a Dynamic Range. See the Dynamic Ranges section of this page: http://www.cpearson.com/excel/named.htm.

[jrose]

I agree, the dynamic range trick is the way I'd prefer to go.
Has the added bonus that anyone else browsing through your workbook could come to the view that you know more than you do **grin**