name dynamic ranges with VBA

For those times when I have many range names to create...perhaps in
several existing workbooks...I use this VBA code in my Personal.XLS workbook
(but you could put it in any workbook):

Copy/Paste the below code into a Genaral Module.

Sub CreateRangesFromList()
'This procedure creates new range names in the active workbook
'using a 2-column range of cells that contains:
'Col 1: the text of the range name to be created
'Col 2: text of the RefersTo definition (in proper format: ="somesheet!someref")
   Dim intRCount As Integer
   Dim intRRef As Integer
   
   Dim strSelAddr As String
   Dim rngBase As Range
   
   On Error GoTo FailTrap
   With selection
      If .Columns.Count <> 2 Then
         MsgBox Title:="Invalid Base Range", _
               Prompt:="Range does not contain exactly 2 columns", _
               Buttons:=vbCritical + vbOKOnly
         Exit Sub
      End If
      intRCount = .Rows.Count
      Set rngBase = .Cells(1, 1)
   End With
   
   With rngBase
         
      For intRRef = 1 To intRCount

         On Error Resume Next
         ActiveWorkbook.Names.Add _
            Name:=.Cells(intRRef, 1).Formula, _
            RefersTo:=.Cells(intRRef, 2).Formula
         If Err.Number <> 0 Then
            MsgBox Title:="Problem with this item:", _
                  Prompt:=.Offset(RowOffset:=intRRef).Address
         End If
      Next intRRef
   End With
   Set rngBase = Nothing
Exit Sub

FailTrap:
   Set rngBase = Nothing
   MsgBox Title:="Problems Encountered", _
         Prompt:="Check list for invalid Range Name Text and/or Refers_To values.", _
         Buttons:=vbCritical + vbOKOnly
         
End Sub

Here's an example of how I'd use it....
First, I create a 2-column list, comprising of:
-Name to be created
-Refers to expression (formatted as text)

Example:
G1: MyDynRangeName
H1: '=OFFSET($C$3,,,COUNTA($C:$C),3)

Select G1:H1
[ALT]+[F8]...a shortcut for <tools><macro><macros>
Select: CreateRangesFromList
Click [Run]

The code will create or rebuild the range names in the list.

In the above example, a dynamic range name is created that
refers to C3:D3 and down for the number of non-blank cells in Col_C.

Is that something you can work with?

Originally Posted by robotlust

Hi,
I've managed to name some dynamic ranges with OFFEST and COUNTA. However I'd really like to manage my ranges with a VBA procedure as there'll be quite a few ranges and the UI method is loathsome.

I found this during some searching but it's not working.

ThisWorkbook.Names.Add Name:="NewName", _ 
        RefersTo:="=$A$1:$C$10", Visible:=True

I have very limited VBA knowledge. (i can do cut, paste & tweak but cant write yet)

Can anyone assist?

Good morning,
the code works for me,
Did you run the code so it can name the range?

If you want dynamic ranges the fast way, check out this site
http://blog.livedoor.jp/andrewe/archives/50353713.html
select a cell
run the macro,
the code will name a dynamic range from the selected cell down

go to insert>name>define, you will find your dynamic named range there

Hi, Cameron

If you select the 2-column range of cells....
Example:

LU_DisplayName		=OFFSET(AdminSheet!$B$5:$B$5,,,COUNTA(AdminSheet!$B:$B)-2)
LU_Name_FileLoc_XRef	=OFFSET(AdminSheet!$B$5:$F$5,,,COUNTA(AdminSheet!$B:$B)-2)
rngDisplayCells_Pic_1	=ShowFilePicsDemo!$B$9:$C$20
rngDisplayCells_Pic_2	=ShowFilePicsDemo!$E$9:$E$20
rngDisplayCells_Pic_3	=ShowFilePicsDemo!$B$23:$C$34
rngDisplayCells_Pic_4	=ShowFilePicsDemo!$E$23:$E$34
rngDisplayName		=ShowFilePicsDemo!$B$6
rngFolderPath		=AdminSheet!$C$3

Then....run the macro one time:
[ALT-[F8]....Select: CreateRangesFromList....Click: Run

Those range names will be created.

What it WON'T do is constantly update Range Names
if the formulas in the above cells change.

Does that help?
Post back if you have more questions.