renaming named ranges

A

AndreasHermle

Dear Experts:

I got a workbook with 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 ... . Ranges named range10, range11 .... range40 etc. are to
be left untouched.

Help is much appreciated.

Thank you very much in advance.

Regards, Andreas
 
C

Claus Busch

Hi Andreas,

Am Mon, 4 Apr 2011 09:34:24 -0700 (PDT) schrieb AndreasHermle:
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 ... . Ranges named range10, range11 .... range40 etc. are to
be left untouched.

Sub RangeNames()
Dim rngName As Name

For Each rngName In ThisWorkbook.Names
If Len(rngName.Name) = 6 Then
rngName.Name = Left(rngName.Name, 5) & _
Format(Right(rngName.Name, 1), "00")
End If
Next
End Sub


Regards
Claus Busch
 
R

Rick Rothstein

I got a workbook with 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 ... . Ranges named range10, range11 ....
range40 etc. are to be left untouched.

I would do it like this...

Sub FillOutRangeNames()
Dim N As Name
For Each N In ThisWorkbook.Names
If UCase(N.Name) Like "RANGE#" Then
N.Name = "Range" & Format(Right(N.Name, 1), "00")
End If
Next
End Sub

Rick Rothstein (MVP - Excel)
 
A

AndreasHermle

Hi Andreas,

Am Mon, 4 Apr 2011 09:34:24 -0700 (PDT) schrieb AndreasHermle:


Sub RangeNames()
Dim rngName As Name

For Each rngName In ThisWorkbook.Names
    If Len(rngName.Name) = 6 Then
        rngName.Name = Left(rngName.Name, 5) & _
             Format(Right(rngName.Name, 1), "00")
    End If
Next
End Sub

Regards
Claus Busch

Hi Claus,

great, works like a charm. Thank you very much for your great and
professional support. Regards, Andreas
 
A

AndreasHermle

I would do it like this...

Sub FillOutRangeNames()
  Dim N As Name
  For Each N In ThisWorkbook.Names
    If UCase(N.Name) Like "RANGE#" Then
      N.Name = "Range" & Format(Right(N.Name, 1), "00")
    End If
  Next
End Sub

Rick Rothstein (MVP - Excel)

Hi Rick,

as always, your codes work just fine. I really appreciate your
professional support.

Thank you very much.

Regards, Andreas
 

Ask a Question

Want to reply to this thread or ask your own question?

You'll need to choose a username for the site, which only take a couple of moments. After that, you can post your question and our members will help you out.

Ask a Question

Top