rename ranges, using VBA

starship

New Member
Joined
May 17, 2004
Messages
2
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???
 

Excel Facts

How to create a cell-sized chart?
Tiny charts, called Sparklines, were added to Excel 2010. Look for Sparklines on the Insert tab.
Welcome to the Board.

Try this:

Code:
Sub Test()
    Dim Nm As Name
    For Each Nm In ThisWorkbook.Names
        Nm.Name = WorksheetFunction.Substitute(Nm.Name, "LgLO", "SST")
    Next Nm
End Sub
 
Upvote 0
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.
 
Upvote 0
Sorry about that - I didn't expect the code to delete the other names.

Try this:

Code:
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
 
Upvote 0
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.
Code:
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.
 
Upvote 0
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.

Code:
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.


Code:
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
 
Upvote 0

Forum statistics

Threads
1,213,517
Messages
6,114,089
Members
448,548
Latest member
harryls

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Back
Top