Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.programming
|
|||
|
|||
renaming named ranges
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 |
#2
Posted to microsoft.public.excel.programming
|
|||
|
|||
renaming named ranges
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 -- Win XP PRof SP2 / Vista Ultimate SP2 Office 2003 SP2 /2007 Ultimate SP2 |
#3
Posted to microsoft.public.excel.programming
|
|||
|
|||
renaming named ranges
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) |
#4
Posted to microsoft.public.excel.programming
|
|||
|
|||
renaming named ranges
On Apr 4, 8:36*pm, Claus Busch wrote:
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 -- Win XP PRof SP2 / Vista Ultimate SP2 Office 2003 SP2 /2007 Ultimate SP2 Hi Claus, great, works like a charm. Thank you very much for your great and professional support. Regards, Andreas |
#5
Posted to microsoft.public.excel.programming
|
|||
|
|||
renaming named ranges
On Apr 4, 8:53*pm, "Rick Rothstein"
wrote: 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) Hi Rick, as always, your codes work just fine. I really appreciate your professional support. Thank you very much. Regards, Andreas |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
Named ranges scope / workbook/worksheet level named ranges- changeswith variable use... | Excel Programming | |||
Excel {2007} Named Ranges: Renaming, Deleting etc... | Excel Discussion (Misc queries) | |||
renaming a named range | Excel Discussion (Misc queries) | |||
Copy data in named ranges to a newer version of the same template to identical ranges | Excel Programming | |||
Systematically Renaming Ranges | Excel Programming |