Results 1 to 7 of 7
  1. #1
    AndySharpe
    Guest

    Named Ranges in Excel

    Does anybody know how to:
    1. Change what cells a range name refers to?
    2. Examine what cells a range name refers to?
    3. Loop through all range names?

    Alternatively, is it possible to access named ranges of one workbook from a worksheet in another workbook?

  2. #2
    Super Moderator WebGenii's Avatar
    Join Date
    Jan 2001
    Location
    Redcliff, Alberta, Canada
    Posts
    4,066
    Thanks
    2
    Thanked 5 Times in 5 Posts

    Re: Named Ranges in Excel

    [img]/w3timages/icons/grin.gif[/img]Well, it just so happens I can answer some of your question, as I'm doing a project on this very thing right now.
    1. Change what cells a range name refers to?
    try:
    ActiveWorkbook.Names.Add Name:="test", RefersToR1C1:="=Sheet1!R6C4:R14C5"
    where test refers to the existing range name. You can use alphanumeric row/column referencing.

    2. Examine what cells a range name refers to?
    By this I assume you mean, navigate to the range itself?
    Try:
    Application.Goto Reference:="test"

    3. Loop through all range names?
    I'd be fascinated to hear a suggestion on that one myself.

    Cheers
    Catharine
    [b]Catharine Richardson (WebGenii)
    WebGenii Home Page
    Moderator: Spreadsheets, Other MS Apps, Presentation Apps, Visual Basic for Apps, Windows Mobile

  3. #3
    Platinum Lounger
    Join Date
    Dec 2000
    Location
    Queanbeyan, New South Wales, Australia
    Posts
    3,730
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Named Ranges in Excel

    In answer to questions 2 & 3:

    Dim xlnname As Excel.Name
    For Each xlnname In Excel.Names
    Debug.Print xlnname.Name; " "; xlnname.RefersTo
    Next
    Subway Belconnen- home of the Signboard to make you smile. Get (almost) daily updates- follow SubwayBelconnen on Twitter.

  4. #4
    Super Moderator WebGenii's Avatar
    Join Date
    Jan 2001
    Location
    Redcliff, Alberta, Canada
    Posts
    4,066
    Thanks
    2
    Thanked 5 Times in 5 Posts

    Re: Named Ranges in Excel

    Cool!
    [b]Catharine Richardson (WebGenii)
    WebGenii Home Page
    Moderator: Spreadsheets, Other MS Apps, Presentation Apps, Visual Basic for Apps, Windows Mobile

  5. #5
    Platinum Lounger
    Join Date
    Dec 2000
    Location
    Queanbeyan, New South Wales, Australia
    Posts
    3,730
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Named Ranges in Excel

    Yeah, it took a little bit of digging to find that when I needed it. I found it eventually by recording the action of creating a named range.

    BTW, your pic would be improved by setting a transparent background. You can do that through many of the paint programs like PaintShop Pro.
    Subway Belconnen- home of the Signboard to make you smile. Get (almost) daily updates- follow SubwayBelconnen on Twitter.

  6. #6
    Super Moderator WebGenii's Avatar
    Join Date
    Jan 2001
    Location
    Redcliff, Alberta, Canada
    Posts
    4,066
    Thanks
    2
    Thanked 5 Times in 5 Posts

    Re: Named Ranges in Excel

    And here I've been spending an unreasonable amount of time trying to get a groovy signature like you![img]/w3timages/icons/laugh.gif[/img]
    [b]Catharine Richardson (WebGenii)
    WebGenii Home Page
    Moderator: Spreadsheets, Other MS Apps, Presentation Apps, Visual Basic for Apps, Windows Mobile

  7. #7
    AndySharpe
    Guest

    Re: Named Ranges in Excel

    Thanks to both of you.

Posting Permissions

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