Text Functions‎ > ‎

Mk Name Ranges

PROBLEM:
"I like to use INDIRECT() functions to grab named ranges for formulas and data validation.  INDIRECT() does not work when the names are maintained with dynamic range formulas, so I create my name ranges manually.  But I have a lot of lists to maintain and it's a pain to manually tweak the ranges.  I need a macro that will create/update all the named ranges on a LISTS sheet all in one go."

SPECIFICATIONS:
  1. To keep things simple, put all your name range "lists" on a single sheet.
  2. Each column is a separate unique list
  3. The value in row1 is the name of the range
  4. The values in rows 2:??? represent the values for that group
  5. It's best not to have blanks in the data, usually unnecessary, too.
                                                                 EXAMPLE 1
 ABCDEFGH
1Name1Name2Name3Name4Name5Name6Name7Name8
2amonAaaa1bbb1aaa2bbb2aaa3
3btueBaaa2bbb2aaa3bbb3aaa4
4cwedCaaa3bbb3aaa4bbb4aaa5
5dthuDaaa4bbb4aaa5bbb5aaa6
6efriEaaa5bbb5aaa6bbb6aaa7
7fsatFaaa6bbb6aaa7bbb7aaa8
8gsunGaaa7bbb7aaa8bbb8aaa9
9h Haaa8bbb8aaa9bbb9aaa10
10i I bbb9aaa10bbb10aaa11
11j   bbb10aaa11bbb11aaa12
12k    aaa12bbb12aaa13
13l    aaa13bbb13 
14     aaa14  
15     aaa15  
16        


                            Sample Sheet:  MakeNameRanges.xls

CODE

Option Explicit

Sub MakeNameRanges()
'Author:    Jerry Beaucaire
'Date:      11/15/2010
'Summary:   using a "lists" page, creates/updates a series of named ranges
Dim Names  As Range
Dim Nm     As Range
Dim NmLR   As Long
Dim ShtStr As String

If MsgBox("Create/Update name ranges from the data on this activesheet?", _
                            vbYesNo, "Update Names") = vbNo Then Exit Sub
Set Names = ActiveSheet.Rows(1).SpecialCells(xlConstants)
ShtStr = "='" & ActiveSheet.Name & "'!"

    For Each Nm In Names
        NmLR = Cells(Rows.Count, Nm.Column).End(xlUp).Row
        ActiveWorkbook.Names.Add Name:=Nm, RefersToR1C1:=ShtStr & _
                    "R2C" & Nm.Column & ":R" & NmLR & "C" & Nm.Column
    Next Nm

    MsgBox "Done"
End Sub




Nothing says "thanks" like a steak dinner!
PayPal - The safer, easier way to pay online!
Comments