+ Reply to Thread
Results 1 to 6 of 6

Setting up dynamic named ranges in code

  1. #1
    Forum Contributor
    Join Date
    03-30-2010
    Location
    Manchester England
    MS-Off Ver
    Excel 2010
    Posts
    992

    Setting up dynamic named ranges in code

    I normally set up my dynamic named ranges in the name manager using formulae like
    Please Login or Register  to view this content.
    I now need to be able to set up these ranges using vba code and have come up with the problem that "Offset" in vba has a different meaning from "Offset" in the DefineName window.

    I could, I suppose, erase the names after every use and redefine them afresh each time I need them, but this seems cumbersome, the ranges will change from time to time and I would like to keep their dynamic nature.
    What is the best way to deal with this in code.
    John

  2. #2
    Forum Guru
    Join Date
    08-05-2004
    Location
    NJ
    MS-Off Ver
    365
    Posts
    13,578

    Re: Setting up dynamic named ranges in code

    Maybe
    Please Login or Register  to view this content.
    Does that work for you?
    There are also ways to create dynamic ranges using INDEX instead of OFFSET
    ChemistB
    My 2?

    substitute commas with semi-colons if your region settings requires
    Don't forget to mark threads as "Solved" (Edit First post>Advanced>Change Prefix)
    If I helped, Don't forget to add to my reputation (click on the little star at bottom of this post)

    Forum Rules: How to use code tags, mark a thread solved, and keep yourself out of trouble

  3. #3
    Forum Guru Jonmo1's Avatar
    Join Date
    03-08-2013
    Location
    Bryan, TX
    MS-Off Ver
    Excel 2010
    Posts
    9,763

    Re: Setting up dynamic named ranges in code

    Quote Originally Posted by j_Southern View Post
    the ranges will change from time to time and I would like to keep their dynamic nature.
    In what way do they change ?
    Perhaps the original formula can be modified to account for that 'time to time adjustment'

  4. #4
    Forum Contributor
    Join Date
    03-30-2010
    Location
    Manchester England
    MS-Off Ver
    Excel 2010
    Posts
    992

    Re: Setting up dynamic named ranges in code

    JonMo1, Thanks for your interest, The ranges will only ever change by increasing the number of rows.
    ChemistB, Here is how I have used your code in my sub routine.
    Please Login or Register  to view this content.
    This does create new ranges with the right names. The RefersTo column in the NamesManager however reads
    =Offset(ws.Name!$A$8,0,0,Counta(ws.Name!$A:$A)-2,41)

    It looks as though ws.name has been treated like a string rather than a variable. I am going to try:-
    Please Login or Register  to view this content.
    And that worked! Thanks for your help
    John

  5. #5
    Forum Guru Jonmo1's Avatar
    Join Date
    03-08-2013
    Location
    Bryan, TX
    MS-Off Ver
    Excel 2010
    Posts
    9,763

    Re: Setting up dynamic named ranges in code

    Quote Originally Posted by j_Southern View Post
    The ranges will only ever change by increasing the number of rows.
    Isn't that the purpose of using COUNTA($A:$A) in your original formula?

  6. #6
    Forum Contributor
    Join Date
    03-30-2010
    Location
    Manchester England
    MS-Off Ver
    Excel 2010
    Posts
    992

    Re: Setting up dynamic named ranges in code

    JonMo1
    Yes I did use counta for that purpose. My original formula was entered manually into the Define Name window. I then needed to modify the original application and I took the opportunity to automate the setting up of a new year. This involved deleting sheets and then creating new ones for the new year. I then had to delete the range names (they are not deleted when the sheet they occupy is deleted). Then I had to reinstate the range names using VBA and this is where I got stuck. All is OK now though as ChemistB's idea, modified so as to use a variable for the sheet Name, worked fine.
    John

+ Reply to Thread

Thread Information

Users Browsing this Thread

There are currently 1 users browsing this thread. (0 members and 1 guests)

Similar Threads

  1. Setting a dynamic Named range
    By hond in forum Excel Programming / VBA / Macros
    Replies: 1
    Last Post: 12-14-2012, 09:57 AM
  2. Dynamic Charts - help setting up with named ranges
    By Alex Aherne in forum Excel General
    Replies: 5
    Last Post: 06-10-2012, 07:17 AM
  3. Named ranges-Should I use a dynamic named range
    By foseco in forum Excel General
    Replies: 4
    Last Post: 06-11-2009, 03:56 PM
  4. Assigning dynamic named ranges to listfillrange via macro code
    By Schwizer in forum Excel Programming / VBA / Macros
    Replies: 2
    Last Post: 01-30-2007, 11:44 AM
  5. [SOLVED] Named ranges vs setting range in code
    By Tim in forum Excel Programming / VBA / Macros
    Replies: 2
    Last Post: 02-23-2006, 10:55 PM

Bookmarks

Posting Permissions

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

Search Engine Friendly URLs by vBSEO 3.6.0 RC 1