rename ranges, using VBA

[starship]

I need to rename a bunch of named ranges, replacing certain parts of the names with other, given parts. For example, I need to rename all of the names that contain LgLO, replacing LgLO with SST. Is there a macro that I can use???

 

[Andrew Poulsom]

Welcome to the Board.

Try this:

Sub Test()
    Dim Nm As Name
    For Each Nm In ThisWorkbook.Names
        Nm.Name = WorksheetFunction.Substitute(Nm.Name, "LgLO", "SST")
    Next Nm
End Sub

 

[starship]

Thanks - the renaming worked just fine. But....

All named ranges that did not contain LgLO were eliminated!

As I look at what I need to do, I realized that I asked the wrong question.

Actually, so that I can edit with the formula's containing the original names, I really need to make duplicate names for the same ranges - ie, I need to create a second set of names that refer to the same ranges but substitute SST for LgLO. Then, when I've edited all of the formulas that refered to the LgLO names and changed them to the SST names, I need to delete all of the LgLO names.

 

[Andrew Poulsom]

Sorry about that - I didn't expect the code to delete the other names.

Try this:

Sub Test()
    Dim Nm As Name
    For Each Nm In ThisWorkbook.Names
        If Nm.Name Like "*LgLO*" Then
            Names.Add WorksheetFunction.Substitute(Nm.Name, "LgLO", "SST"), Nm.RefersTo
        End If
    Next Nm
End Sub

 

[ctrlaltdel]

So it looks like Mr. Poulsom's second code simply adds the new name but deletes nothing. Based on your explanation, you can now edit all of your formulas...unless you wanted to do that automatically as well.

Once complete, this code should work to delete all the left over old named ranges.

Sub DLTNM() 
    Dim Nm As Name 
    For Each Nm In ThisWorkbook.Names 
        If Nm.Name Like "*LgLO*" Then 
            Nm.Delete
        End If 
    Next Nm 
End Sub

I didn't test it, but it should work.

 

[TakenItEasy]

I need to rename a bunch of named ranges, replacing certain parts of the names with other, given parts. For example, I need to rename all of the names that contain LgLO, replacing LgLO with SST. Is there a macro that I can use???

Sorry for the sick bump but I didn't find any alternative solutions posted anywhere so I thought I'd update this.

Using 2010, I used Andrew Poulsom's two partial solutions and just pieced them together. I realized that it was the if statement which set up the isolating conditions for the target names from all other names which, I guess the substitute alone failed to do. Once I realized that, I just used Andrew's first method with the protection of the If statement in his second method to change the exact names I wanted.

Sub Test1()
    Dim Nm As Name
    For Each Nm In ThisWorkbook.Names
        If Nm.Name Like "aaaa_*" Then
            Nm.Name = WorksheetFunction.Substitute(Nm.Name, "aaaa", "bb")
        End If
    Next Nm
End Sub

To make changes to a unique part of a long list of names which can't be handled globally, I used a case statement which also set up conditions to target only what I wanted to change.

Sub Test2()
    Dim Nm As Name
    For Each Nm In ThisWorkbook.Names
        Select Case Nm.Name
        Case "Test_1"
            Nm.Name = WorksheetFunction.Substitute(Nm.Name, "Test_1", "Test_1A")
        Case "Test_2"
            Nm.Name = WorksheetFunction.Substitute(Nm.Name, "Test_2", "Test_1B")
        Case "Test_3"
            Nm.Name = WorksheetFunction.Substitute(Nm.Name, "Test_3", "Test_1C")
        Case "Test_4"
            Nm.Name = WorksheetFunction.Substitute(Nm.Name, "Test_4", "Test_1D")
        Case "Test_5"
            Nm.Name = WorksheetFunction.Substitute(Nm.Name, "Test_5", "Test_1E")
        Case "Test_6"
            Nm.Name = WorksheetFunction.Substitute(Nm.Name, "Test_6", "Test_1F")
        End Select
    Next Nm
End Sub

Notice that I intentionally only changed half of the test names to make sure it wouldn't do anything unexpected.

From: TO
aaaa_1: bb_1
aaaa_2: bb_2
aaaa_3: bb_3
aaaa_4: bb_4
aaaa_5: bb_5
aaaa_6: bb_6
Test_1: Test_1A
Test_2: Test_1B
Test_3: Test_1C
Test_4: Test_1D
Test_5: Test_1E
Test_6: Test_1F
Test_7: Test_7
Test_8: Test_8
Test_9: Test_9
Test_10: Test_10
Test_11: Test_11
Test_12: Test_12