Results 1 to 5 of 5
  1. #1
    3 Star Lounger
    Join Date
    Aug 2001
    Location
    Seattle, Washington
    Posts
    251
    Thanks
    29
    Thanked 2 Times in 2 Posts

    Enlarge range Name (Excel XP)

    <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.
    Bob Wall

  2. #2
    WS Lounge VIP sdckapr's Avatar
    Join Date
    Jul 2002
    Location
    Pittsburgh, Pennsylvania, USA
    Posts
    11,225
    Thanks
    14
    Thanked 342 Times in 335 Posts

    Re: Enlarge range Name (Excel XP)

    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

  3. #3
    Platinum Lounger
    Join Date
    Feb 2002
    Location
    A Magic Forest in Deepest, Darkest Kent
    Posts
    5,681
    Thanks
    0
    Thanked 1 Time in 1 Post

    Re: Enlarge range Name (Excel XP)

    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.
    Jerry

  4. #4
    Uranium Lounger
    Join Date
    Dec 2000
    Location
    Salt Lake City, Utah, USA
    Posts
    9,508
    Thanks
    0
    Thanked 6 Times in 6 Posts

    Re: Enlarge range Name (Excel XP)

    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.
    -John ... I float in liquid gardens
    UTC -7ąDS

  5. #5
    3 Star Lounger
    Join Date
    Jan 2004
    Location
    Birmingham, West Midlands, United Kingdom
    Posts
    219
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Enlarge range Name (Excel XP)

    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**

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •