VBA Defining Multiple Named Ranges

Question

I am working with a sheet with almost 200 named ranges (each column is a NR). I now would like to make them dynamic i.e. instead of defining them like

PersonID = =RawData!$A$2:$A$100

I want to do it this way

PersonID = OFFSET(RawData!$A$2,0,0,COUNTA(RawData!$A:$A),1)

But I do not want to do this manually! Is there a way to do this in a texteditor outside Excel or is there a way to do this programatically? I already have the 200 NRs done in the first way in place, but the thought of manually go through them all to change is scaring me.

Jean-François Corbett · Accepted Answer · 2015-03-02 09:50:38Z

1

You can do it in VBA. Example to create a new name:

ActiveWorkbook.Names.Add Name:="PersonID", _
    RefersTo:="=OFFSET(RawData!$A$2,0,0,COUNTA(RawData!$A:$A),1)"

To edit an already existing name:

ActiveWorkbook.Names("PersonID").RefersTo = _
    "=OFFSET(RawData!$A$2,0,1,COUNTA(RawData!$A:$A),1)"

You indicate in a comment that you would also like to iterate through all named ranges to facilitate changing their definition. To loop through all names you can do this:

Dim nm As Name
For Each nm In ActiveWorkbook.Names
    Debug.Print nm.Name
Next nm

or this:

Dim i As Long
For i = 1 To ActiveWorkbook.Names.Count
    Debug.Print ActiveWorkbook.Names.Item(i).Name
Next i

edited Mar 2, 2015 at 9:50

answered Mar 2, 2015 at 8:04

Jean-François Corbett

38.7k30 gold badges146 silver badges192 bronze badges

Sign up to request clarification or add additional context in comments.

4 Comments

Mats Olsson Over a year ago

OK Thanks! Great! This will take me part of the way. I guess I can read out the RefersTo as a string an search for which column it is already defined for and then build my new string and assign this? Also Can I iterate through the collection of names?

Mats Olsson Over a year ago

Just realised that I would like to not be specific in the names of the named ranges! I cannot enter all names! There has to be a way to iterate through all Named Ranges, regardless of their names and exchange the content?

Mats Olsson Over a year ago

Sorry I am new to this forum! Did not know I could or should do that! I am now officially greatful for your help!

Mats Olsson Over a year ago

Thanks Jean-Francois! I will see what I come up with based on this and post it here for you to improve? I will look at the tour ....

Mats Olsson · Accepted Answer · 2015-03-02 13:45:17Z

This seems to be a pretty good tool to have in your toolbox?

Sub MakeRangesDynamic()
Dim i As Long

For i = 1 To ActiveWorkbook.Names.Count
  If Not (ActiveWorkbook.Names.Item(i).Name = "NameToExclude1" Xor _
          ActiveWorkbook.Names.Item(i).Name = "NameToExclude2" Xor _
          ActiveWorkbook.Names.Item(i).Name = "NameToExclude3") Then

    FindTheColumn = Mid$(ActiveWorkbook.Names.Item(i).RefersTo, 11, 2)

    If Mid$(FindTheColumn, 2, 1) = "$" Then
        FindTheColumn = Mid$(FindTheColumn, 1, 1)
    Else
        FindTheColumn = Mid$(FindTheColumn, 1, 2)
    End If

    DynNameString = "=OFFSET(RawData!$" & FindTheColumn & "$2,0,0,COUNTA(RawData!$" & FindTheColumn & ":$" & FindTheColumn & "),1)"
    Debug.Print DynNameString
    'ActiveWorkbook.Names.Item(i).Name.RefersTo = DynNameString
  End If
Next i

End Sub

A special thanks goes to Jean-Francois for helping me out.

Change the RawData to your sheetname and the NameToExclude to your ranges to leave untouched. Remove the last comment for making it happen! But be sure to make a backup copy first!!!!

Collectives™ on Stack Overflow

VBA Defining Multiple Named Ranges

2 Answers 2

4 Comments

Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

2 Answers 2

4 Comments

Comments

Your Answer

Sign up or log in

Post as a guest

Related