+ Reply to Thread
Results 1 to 9 of 9

Need Macro to Create Dynamic Named Ranges

  1. #1
    Registered User
    Join Date
    04-28-2015
    Location
    Sydney, Australia
    MS-Off Ver
    Office 365
    Posts
    60

    Need Macro to Create Dynamic Named Ranges

    I need a Macro to Create Dynamic named Ranges on a cost schedule based on all the codes Ive entered in to column B, example (AB1, AB2, AB3 etc) these codes are also entered in column H additionally.
    I need for the each Named Range to Start from the row position of the code in Column B in Column A (eg "AB1" is Cell B1 but need the Range to Start in A1) , then ending in Column H where that code is repeated (eg "AB1" in Cell H3) and so on for the other codes AB2, AB3 etc

    Table Example.jpg

  2. #2
    Forum Guru
    Join Date
    07-25-2011
    Location
    Florida
    MS-Off Ver
    Excel 2003
    Posts
    9,643

    Re: Need Macro to Create Dynamic Named Ranges

    Cannot have named ranges that are the same as cell addresses. So AB1, AB2 are not valid names.

    The code below creates names MyAB1, MyAB2... etc. You can change it to suit.

    Please Login or Register  to view this content.
    Surround your VBA code with CODE tags e.g.;
    [CODE]your VBA code here[/CODE]
    The # button in the forum editor will apply CODE tags around your selected text.

  3. #3
    Registered User
    Join Date
    04-28-2015
    Location
    Sydney, Australia
    MS-Off Ver
    Office 365
    Posts
    60

    Re: Need Macro to Create Dynamic Named Ranges

    Sorry AB1 and AB2 were bad examples. The cells will contain mixed codes which wont be the same as cell names eg. H1K2 for example. So i neeed the actual text H1K2 in the Named Range
    Last edited by qwertyyy; 01-18-2019 at 12:49 AM.

  4. #4
    Forum Guru
    Join Date
    07-25-2011
    Location
    Florida
    MS-Off Ver
    Excel 2003
    Posts
    9,643

    Re: Need Macro to Create Dynamic Named Ranges

    Please Login or Register  to view this content.

  5. #5
    Registered User
    Join Date
    04-28-2015
    Location
    Sydney, Australia
    MS-Off Ver
    Office 365
    Posts
    60

    Re: Need Macro to Create Dynamic Named Ranges

    Ive Modded the code to ad and "_" as the name match the cell Addresses but it hangs up on one cell value with "-" in it. e.g G2-H2, Is There anyway to replace the minus with an Underscore with the macro

    Sub Dynamic_Named_Ranges()
    Dim cell As Range, rngEnd As Range
    For Each cell In Columns("B").SpecialCells(xlCellTypeConstants)
    Set rngEnd = Columns("H").Find(cell.Value, , xlValues, xlWhole, 1, 1, 0)
    If Not rngEnd Is Nothing Then
    ThisWorkbook.Names.Add Name:=cell.Value & "_", RefersTo:=Range(cell, rngEnd).EntireRow.Resize(, 8)
    End If
    Next cell
    End Sub
    Last edited by qwertyyy; 01-20-2019 at 07:38 PM.

  6. #6
    Forum Guru
    Join Date
    07-25-2011
    Location
    Florida
    MS-Off Ver
    Excel 2003
    Posts
    9,643

    Re: Need Macro to Create Dynamic Named Ranges

    Please Login or Register  to view this content.

  7. #7
    Registered User
    Join Date
    04-28-2015
    Location
    Sydney, Australia
    MS-Off Ver
    Office 365
    Posts
    60

    Re: Need Macro to Create Dynamic Named Ranges

    Im now using the following code , I just cant get the Ranges to start in column B instead of column A


    Please Login or Register  to view this content.

  8. #8
    Forum Guru
    Join Date
    07-25-2011
    Location
    Florida
    MS-Off Ver
    Excel 2003
    Posts
    9,643

    Re: Need Macro to Create Dynamic Named Ranges

    Delete EntireRow from this line of code

    Please Login or Register  to view this content.

  9. #9
    Registered User
    Join Date
    04-28-2015
    Location
    Sydney, Australia
    MS-Off Ver
    Office 365
    Posts
    60

    Re: Need Macro to Create Dynamic Named Ranges

    Works fine now
    Last edited by qwertyyy; 01-11-2023 at 04:43 AM.

+ 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] Dynamic Named Ranges used create a Chart
    By sdingman in forum Excel Programming / VBA / Macros
    Replies: 2
    Last Post: 08-27-2017, 10:13 AM
  2. Create two dynamic named ranges in the same column?
    By nobodyukno in forum Excel General
    Replies: 1
    Last Post: 05-05-2017, 11:10 AM
  3. Quickly Create 100+ Dynamic Named Ranges
    By dharmon in forum Excel Formulas & Functions
    Replies: 3
    Last Post: 06-17-2016, 05:13 PM
  4. [SOLVED] Create Multiple Dynamic Named Ranges with a Macro
    By bennank in forum Excel Programming / VBA / Macros
    Replies: 1
    Last Post: 12-09-2012, 12:55 PM
  5. [SOLVED] Using named ranges to create dynamic charts in Excel 2003
    By BNCOXUK in forum Excel General
    Replies: 2
    Last Post: 11-01-2012, 10:59 AM
  6. [SOLVED] How to create multiple Dynamic Named Ranges at once? and Limit in excel 2010
    By vishnu01445 in forum Excel Formulas & Functions
    Replies: 4
    Last Post: 08-31-2012, 09:28 AM
  7. [SOLVED] Can you create dynamic named ranges containing array formulas?
    By Marie J-son in forum Excel Programming / VBA / Macros
    Replies: 1
    Last Post: 02-28-2006, 09:10 AM

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