• Status: Solved
  • Priority: Medium
  • Security: Public
  • Views: 241
  • Last Modified:

renaming named ranges

Dear Experts:

I got a lot of named ranges. They are named range1, range2, range3, range4 .....range40.

Is it possible to add a zero before the single digit named ranges (using VBA) so that they look like as follows: range01, range02, ... range09.

Help is much appreciated.

Thank you very much in advance.

Regards, Andreas
0
Andreas Hermle
Asked:
Andreas Hermle
  • 5
  • 5
1 Solution
 
Chris BottomleySoftware Quality Lead EngineerCommented:
Try the following ... on a copy first, (I did test but there may still be issues I didn't find)

renames is the sub to call

Chris
Sub renames()
Dim rng As Name

    For Each rng In ActiveWorkbook.Names
        Debug.Print rng.Name
        rng.Name = RegExpReplace(rng.Name, "Range([1-9])", "Range0$1", True, False)
        Debug.Print rng.Name
    Next

End Sub

Function RegExpReplace(LookIn As String, PatternStr As String, Optional ReplaceWith As String = "", _
    Optional ReplaceAll As Boolean = True, Optional MatchCase As Boolean = True)
    ' This function uses Regular Expressions to parse a string, and replace parts of the string
    ' matching the specified pattern with another string.  The optional argument ReplaceAll controls
    ' whether all instances of the matched string are replaced (True) or just the first instance (False)
    
    ' By default, RegExp is case-sensitive in pattern-matching.  To keep this, omit MatchCase or
    ' set it to True
    
    ' If you use this function from Excel, you may substitute range references for all the arguments
    
    Static RegX As Object
    
    If RegX Is Nothing Then Set RegX = CreateObject("VBScript.RegExp")
    With RegX
        .Pattern = PatternStr
        .Global = ReplaceAll
        .IgnoreCase = Not MatchCase
    End With
    
    RegExpReplace = RegX.Replace(LookIn, ReplaceWith)
        
End Function

Open in new window

0
 
Andreas HermleTeam leaderAuthor Commented:
Hi Chris,

thank you very much for yours swift support.

I am afraid  to tell  you that your a macro adds the zero in front of all the numbers that form part of the named ranges, i.e. range01,  range02 ....range020 (instead of just range20).

As I described in my macro requirements, I would like just the named ranges changed where there is a SINGLE digit number, the others such as 'range11' or 'range40' are to be left untouched.

Is it possible to fix  this? What do you think?

Help is much appreciated.  Regards,  Andreas
0
 
Chris BottomleySoftware Quality Lead EngineerCommented:
That shouldn't happen ... I did test to ensure

range1 through 9 are modified and
range01, range10 etc are not

Did I misunderstand or is it your experience that everything is renamed?

Chris
0
Cloud Class® Course: CompTIA Cloud+

The CompTIA Cloud+ Basic training course will teach you about cloud concepts and models, data storage, networking, and network infrastructure.

 
Andreas HermleTeam leaderAuthor Commented:
Hi Chris,

it is my experience that everything is renamed. Please see attached screenshot. Strange isn't it, because I believe you that is working on your side.

Regards, Andreas renamed_ranges_0
0
 
Chris BottomleySoftware Quality Lead EngineerCommented:
Are all your ranges 1 to 99 in scope? i.e all less that 100

Chris
0
 
Andreas HermleTeam leaderAuthor Commented:
Hi Chris,

yes, they are less than 100

Andreas
0
 
Chris BottomleySoftware Quality Lead EngineerCommented:
I'm relooking to see it should work for me and not you.

Chris
0
 
Andreas HermleTeam leaderAuthor Commented:
Hi Chris,

take your time.

Regards, Andreas
0
 
Chris BottomleySoftware Quality Lead EngineerCommented:
Forget the clever code ... i'm too dumb!

Simplified below for the specific condition ... see if this is any better.

Chris
Sub renames()
Dim rng As Name
Dim str As String

    For Each rng In ActiveWorkbook.Names
        str = Replace(rng.Name, "range", "", , , vbTextCompare)
        Debug.Print rng.Name
        If IsNumeric(str) Then
            If str >= 1 And str <= 9 Then _
                rng.Name = Replace(rng.Name, str, Format(str, "00"))
        End If
        Debug.Print rng.Name
    Next

End Sub

Open in new window

0
 
Andreas HermleTeam leaderAuthor Commented:
Chris,

great, this did the trick!. Thank you very much for your professional help. Regards, Andreas
0
Question has a verified solution.

Are you are experiencing a similar issue? Get a personalized answer when you ask a related question.

Have a better answer? Share it in a comment.

Join & Write a Comment

Featured Post

Free Tool: Port Scanner

Check which ports are open to the outside world. Helps make sure that your firewall rules are working as intended.

One of a set of tools we are providing to everyone as a way of saying thank you for being a part of the community.

  • 5
  • 5
Tackle projects and never again get stuck behind a technical roadblock.
Join Now