Renaming Ranges
Manage your personal finances in Excel
Thanks Thanks:  0
Likes Likes:  0
Page 1 of 2 12 LastLast
Results 1 to 10 of 16

Thread: Renaming Ranges

  1. #1
    New Member
    Join Date
    Mar 2002
    Location
    England
    Posts
    46
    Post Thanks / Like
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    Default

     
    Hello.
    I am trying to write some code that deletes a name from a range. then re-assigns that name to another.

    More specifically.

    A B C
    1 2 3
    2 2 7
    6 7 8
    9 8 2

    The range of cells above would change each time another macro is run. There may be more or less rows of data. So it could be:

    A B C
    1 2 3
    2 2 7
    6 7 8
    9 8 2
    8 9 4
    5 6 4

    What i want to do is rename the range each time to account for the new rows.

    I have been trying for hours. can anyone help me?

  2. #2
    MrExcel MVP Barrie Davidson's Avatar
    Join Date
    Feb 2002
    Location
    Winnipeg
    Posts
    2,330
    Post Thanks / Like
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    Default

    Use:

    Range("A1", Range("C65536").End(xlUp).Address).Name = "Waggy30"


    Regards,
    Barrie Davidson

    "You're only given a little spark of madness. You mustn't lose it." - Robin Williams

  3. #3
    MrExcel MVP
    Join Date
    Feb 2002
    Location
    Sydney, Australia
    Posts
    2,936
    Post Thanks / Like
    Mentioned
    1 Post(s)
    Tagged
    0 Thread(s)

    Default

    Hi,

    This code will add a named range for the current region. Say you had data in range A1:C3 and then you ran the macro it will create a named range AnyNameYouLike referring to that area. Now, if data is added to cell A4, the macro reran it will now refer to A1:C4.

    Let me know if that isn't right,

    Regards,
    D

    Code:
    Sub CreateNamedRange()
    ActiveWorkbook.Names.Add "AnyNameYouLike", "=" & ActiveSheet.Name & _
                    "!" & Selection.CurrentRegion.Address, True
    End Sub

  4. #4
    New Member
    Join Date
    Feb 2002
    Location
    Dallas, Texas
    Posts
    33
    Post Thanks / Like
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    Default

    Have you considered using the OFFSET and COUNT functions to construct dynamic references that update automatically as data are added?

    This sounds like it might do what you want.

  5. #5
    MrExcel MVP Aladin Akyurek's Avatar
    Join Date
    Feb 2002
    Location
    The Hague, NL
    Posts
    81,854
    Post Thanks / Like
    Mentioned
    12 Post(s)
    Tagged
    1 Thread(s)

    Default

    On 2002-03-09 08:38, waggy30 wrote:
    Hello.
    I am trying to write some code that deletes a name from a range. then re-assigns that name to another.

    More specifically.

    A B C
    1 2 3
    2 2 7
    6 7 8
    9 8 2

    The range of cells above would change each time another macro is run. There may be more or less rows of data. So it could be:

    A B C
    1 2 3
    2 2 7
    6 7 8
    9 8 2
    8 9 4
    5 6 4

    What i want to do is rename the range each time to account for the new rows.

    I have been trying for hours. can anyone help me?
    Another way to achieve what you want.

    Lets say that A2:C7 in Sheet1 houses your first sample data (I suppose A1:C1 will house descriptive labels).


    Activate Insert|Name|Define.

    Enter 'EndRow' in the "Names in Workbook" box.

    Enter the following formula in the "Refers to" box:

    =OFFSET(Sheet1!$A$2,0,0,MATCH(9.99999999999999E+307,Sheet1!$A:$A),1)

    I assume here that data in column A in Sheet1 is of numeric type (numbers or dates). If this is not the case, replace A with the column identifier of another column which meets the condition of being of numeric type.

    Click Add.

    While still in the Define Name window,

    enter SDATA (from Source Data) in the Names in Workbook box;

    enter as formula in the Refers to box:

    =OFFSET(Sheet1!$A$2,0,0,EndRow-1,3)

    Click OK.

    -1 in the EndRow-1 bit "strips off" the row that contains labels from the definition of the range.

    Try as an exercise:

    =VLOOKUP(2,SDATA,3,0)

    Try also adding more rows of data or deleting rows from Sheet1.


    [ This Message was edited by: Aladin Akyurek on 2002-03-11 08:46 ]

  6. #6
    Guest

    Default

    You can also define a dynamic range, which will change as you enter data.

    If you go to Insert-Name-Define, type in a name for the range, the type the following formula in the Refers To box:

    =OFFSET(Sheet2!$A$1,,,COUNTA(Sheet2!$A:$A),COUNTA(Sheet2!
    $1:$1))

    Note: if you data does not start in cell A1, then you will have to adjust this. Let me know if it does not.

    Hope this helps,

    Russell

  7. #7
    MrExcel MVP Russell Hauf's Avatar
    Join Date
    Feb 2002
    Location
    Portland, OR Area - USA
    Posts
    1,605
    Post Thanks / Like
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    Default

    Sorry, the last message was from me.

  8. #8
    Board Regular
    Join Date
    Feb 2002
    Location
    Georgia USA
    Posts
    569
    Post Thanks / Like
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    Default

    Russell,I am trying to use your formula for a name range it works fine for column a I need it for column C, I thought I could just change the numbers on the end but that is not working for me. Also is there any way to paste in the refers to box? Thanks

  9. #9
    New Member
    Join Date
    Mar 2002
    Location
    England
    Posts
    46
    Post Thanks / Like
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    Default

    No, Russell the first row of the actual data is F25:H25. Would it still work if i want multiple columns?


  10. #10
    New Member
    Join Date
    Mar 2002
    Location
    England
    Posts
    46
    Post Thanks / Like
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    Default

      
    Barry, can you explain what your code is doing. So i can adapt it to my real situation (the data isn't in the cells i used an example). I tried your code with the example I gave and it worked, but i can't adapt it.

    Thanks

User Tag List

Like this thread? Share it with others

Like this thread? Share it with others

Posting Permissions

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

 

 
DMCA.com