Error Checking in Excel
Thanks Thanks:  0
Likes Likes:  0
Page 2 of 2 FirstFirst 12
Results 11 to 16 of 16

Thread: Renaming Ranges

  1. #11
    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

    On 2002-03-09 12:46, waggy30 wrote:
    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
    Sure,

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

    is applying the name "Waggy30" the range where the top left cell is A1 and the bottom right cell is the last cell in column C. The statement Range("C65536").End(xlUp).Address is equivalent to going to C65536, pressing the END key, and then pressing the ARROW UP key. The last part (.Address) simply returns the address of that cell.

    Is that explanation sufficient?



    Barrie Davidson

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

  2. #12
    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

    PS - based on your information, you could use this:

    Range("F25", Range("H65536").End(xlUp).Address).Name = "Waggy30"



    Barrie Davidson

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

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

    Default

    Thanks for that. I managed to get it to work now.

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

    Default

    yep, thats what i did

  5. #15
    MrExcel MVP Ivan F Moala's Avatar
    Join Date
    Feb 2002
    Location
    Auckland, New Zealand
    Posts
    4,209
    Post Thanks / Like
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    Default

    On 2002-03-09 12:46, waggy30 wrote:
    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
    Barry
    Hope you don't mind

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

    "A1" is the Top left cell address of your Rg
    Range("C65536") is the LAST address of the
    Bottom right of your COLUMN so if your last
    column is @ z then change Z for c....

    Ivan

  6. #16
    Guest

    Default

    On 2002-03-09 11:23, Paul B wrote:
    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
    The formula I wrote for the name,

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

    Works only for a range starting in A1 and extending down/across. If you have a range starting in column C, you can change the first 2 arguments to your C column, but the 3rd argument that is given,

    COUNTA(Sheet2!$1:$1)

    counts the number of columns in row 1 that have a value. If you know exactly how many columns your data has but just want your range to extend downward when you enter new data, you can just put the number of columns for this argument, something like this (if your data has 3 columns, or columns C, D, and E):

    =OFFSET(Sheet2!$C$1,,,COUNTA(Sheet2!$C:$C),3)

    Hope this helps (and this should help you too, Waggy),

    Russell

Some videos you may like

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
  •