Results 1 to 5 of 5

rename ranges, using VBA

This is a discussion on rename ranges, using VBA within the Excel Questions forums, part of the Question Forums category; I need to rename a bunch of named ranges, replacing certain parts of the names with other, given parts. For ...

  1. #1
    New Member
    Join Date
    May 2004
    Location
    Corvallis, OR
    Posts
    2

    Default rename ranges, using VBA

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

  2. #2
    MrExcel MVP
    Moderator
    Andrew Poulsom's Avatar
    Join Date
    Jul 2002
    Posts
    67,896

    Default Re: rename ranges, using VBA

    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

  3. #3
    New Member
    Join Date
    May 2004
    Location
    Corvallis, OR
    Posts
    2

    Default Re: rename ranges, using VBA

    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.

  4. #4
    MrExcel MVP
    Moderator
    Andrew Poulsom's Avatar
    Join Date
    Jul 2002
    Posts
    67,896

    Default Re: rename ranges, using VBA

    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

  5. #5
    Board Regular
    Join Date
    Dec 2002
    Posts
    60

    Default Re: rename ranges, using VBA

    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.

Like this thread? Share it with others

Like this thread? Share it with others

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •  


DMCA.com