CODEWITHSUNDEEP
excelvbarangenamed
Mats Olsson
Mats Olsson

Reputation: 101

VBA Defining Multiple Named Ranges

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.

Upvotes: 1

Views: 1642

Answers (2)

Mats Olsson
Mats Olsson

Reputation: 101

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!!!!

Upvotes: 0

Jean-François Corbett
Jean-François Corbett

Reputation: 38500

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

Upvotes: 1

Related Questions