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
Andreas HermleTeam leaderAsked:
Who is Participating?

[Product update] Infrastructure Analysis Tool is now available with Business Accounts.Learn More

x
I wear a lot of hats...

"The solutions and answers provided on Experts Exchange have been extremely helpful to me over the last few years. I wear a lot of hats - Developer, Database Administrator, Help Desk, etc., so I know a lot of things but not a lot about one thing. Experts Exchange gives me answers from people who do know a lot about one thing, in a easy to use platform." -Todd S.

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
Why Diversity in Tech Matters

Kesha Williams, certified professional and software developer, explores the imbalance of diversity in the world of technology -- especially when it comes to hiring women. She showcases ways she's making a difference through the Colors of STEM program.

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

Experts Exchange Solution brought to you by

Your issues matter to us.

Facing a tech roadblock? Get the help and guidance you need from experienced professionals who care. Ask your question anytime, anywhere, with no hassle.

Start your 7-day free trial
Andreas HermleTeam leaderAuthor Commented:
Chris,

great, this did the trick!. Thank you very much for your professional help. Regards, Andreas
0
It's more than this solution.Get answers and train to solve all your tech problems - anytime, anywhere.Try it for free Edge Out The Competitionfor your dream job with proven skills and certifications.Get started today Stand Outas the employee with proven skills.Start learning today for free Move Your Career Forwardwith certification training in the latest technologies.Start your trial today
Microsoft Excel

From novice to tech pro — start learning today.