+ Reply to Thread
Results 1 to 2 of 2

Excel VBA: Created Named Range from cell.value

  1. #1
    Registered User
    Join Date
    10-18-2012
    Location
    Fort Worth, Texas
    MS-Off Ver
    Excel 2007
    Posts
    14

    Excel VBA: Created Named Range from cell.value

    Thought I would share this VBA that creates named ranges from the value of the cell. I hunted for this solution (about 100 different google searchs) and finally just figured it out.
    What is cool about this script is that no matter where the "named range" is in the worksheet, can be created as it is not based on a absolute range reference. This works really well if you have the same named ranged that are used in other calculations, and has saved my sanity!
    If anyone has ideas on how to make it more efficient that would be great!

    Please Login or Register  to view this content.

  2. #2
    Forum Guru TMS's Avatar
    Join Date
    07-15-2010
    Location
    The Great City of Manchester, NW England ;-)
    MS-Off Ver
    MSO 2007,2010,365
    Posts
    43,973

    Re: Excel VBA: Created Named Range from cell.value

    This:

    Please Login or Register  to view this content.

    could be this:

    Please Login or Register  to view this content.

    Otherwise, there's no point checking the cell value as the range will stop at the first blank cell anyway.

    This:

    Please Login or Register  to view this content.

    Perhaps should be this:

    Please Login or Register  to view this content.

    If not, if you have only a header but nothing below it, you will get a Named Range which will include the header and the first blank cell below it.

    I'm not sure what you mean when you say:

    Dynamic as named ranges are not absolute

    The Named Ranges that you are creating are absolute. For example:
    Formula: copy to clipboard
    Please Login or Register  to view this content.
    based on a header in cell A1 and three rows of "data" is absolute.

    Maybe try this to create Dynamic Named Ranges:

    Please Login or Register  to view this content.

    Regards, TMS
    Trevor Shuttleworth - Retired Excel/VBA Consultant

    I dream of a better world where chickens can cross the road without having their motives questioned

    'Being unapologetic means never having to say you're sorry' John Cooper Clarke


+ 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. [SOLVED] Determining if the value of a cell can be a named range, then assigning named ranges after
    By Romulo in forum Excel Programming / VBA / Macros
    Replies: 5
    Last Post: 09-15-2013, 06:05 PM
  2. Named Range in sheet created by Macro
    By HeyLucy in forum Excel Programming / VBA / Macros
    Replies: 3
    Last Post: 02-03-2013, 08:52 AM
  3. Replies: 0
    Last Post: 11-23-2012, 01:27 AM
  4. Replies: 1
    Last Post: 06-03-2006, 10:55 PM
  5. If any cell in named range = 8 then shade named range
    By JJ in forum Excel Programming / VBA / Macros
    Replies: 3
    Last Post: 08-26-2005, 07:05 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