Results 1 to 5 of 5
  1. #1
    2 Star Lounger
    Join Date
    Apr 2001
    Location
    Vancouver, Canada
    Posts
    131
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Creating a Named Range within a macro (Excel 2000 SR1)

    I want to have a portion of one of my macros update/recreate a named range called DB

    I've recorded the code below but don't know how to change it so that it doesn't point to a specific range (the RC addresses). Can someone help me?

    Selection.CurrentRegion.Select
    ActiveWorkbook.Names.Add Name:="DBTest", RefersToR1C1:= _
    "=Accounts!R1C7:R163C60"

    Thanks in advance,

    Christa

  2. #2
    Gold Lounger
    Join Date
    Feb 2001
    Location
    Dublin, Ireland, Republic of
    Posts
    2,697
    Thanks
    1
    Thanked 0 Times in 0 Posts

    Re: Creating a Named Range within a macro (Excel 2000 SR1)

    Does <pre> ActiveWorkbook.Names.Add Name:="DBTest", _
    RefersTo:=ActiveCell.CurrentRegion</pre>

    do what you want ?

    Andrew C

  3. #3
    Gold Lounger
    Join Date
    Feb 2001
    Location
    Dublin, Ireland, Republic of
    Posts
    2,697
    Thanks
    1
    Thanked 0 Times in 0 Posts

    Re: Creating a Named Range within a macro (Excel 2000 SR1)

    Or an alternative <pre> ActiveCell.CurrentRegion.Name = "DbTest"</pre>

    Andrew C

  4. #4
    2 Star Lounger
    Join Date
    Apr 2001
    Location
    Vancouver, Canada
    Posts
    131
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Creating a Named Range within a macro (Excel 2000 SR1)

    Andrew,

    Yes, it does...thanks so much for all your help!

  5. #5
    3 Star Lounger
    Join Date
    Mar 2001
    Location
    Silicon Valley, California, USA
    Posts
    273
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Creating a Named Range within a macro (Excel 2000 SR1)

    However, be careful. If the range you want to be named is contiguous with other cells that you don't want the range name to cover, the CurrentRegion will expand to include them as well. If you use this code, make sure the entire range stays isolated from other blocks.

    If that is not possible, other approaches are necessary, and the details depend on the situation.

Posting Permissions

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