PDA

View Full Version : [SOLVED] Rename my range name



ilyaskazi
10-07-2005, 04:40 AM
In my workbook Sheet name is "INDEX1"

Range("A1") name is "MyRange_1"

I want to rename "MyRange_1" to "MyRange_2" through vba

Bob Phillips
10-07-2005, 05:08 AM
In my workbook Sheet name is "INDEX1"

Range("A1") name is "MyRange_1"

I want to rename "MyRange_1" to "MyRange_2" through vba

there is no rename capability, you need to delete the name and create a new one.

ilyaskazi
10-07-2005, 05:26 AM
Help me with this...



Sub ChangeNames()
Dim NameX As Name
Dim MyRange As New Collection
Dim myString
For Each NameX In Names
If Len(ActiveWorkbook.Names(NameX.Name).Name) > 8 Then
If Left(ActiveWorkbook.Names(NameX.Name).Name, Len(ActiveWorkbook.Names(NameX.Name).Name) - 6) = "SPM" Then
MyRange.Add (ActiveWorkbook.Names(NameX.Name).Name) '# See comment below
End If
End If
Next NameX
For i = 1 To MyRange.count
myString = myString & MyRange.Item(i) & Chr(10)
Next i
MsgBox myString
Set MyRange = Nothing
End Sub

#= Can i store here cell address of found range name???

mvidas
10-07-2005, 06:48 AM
Hello,
The following should work fine for you:


Function RenameNamedRange(ByVal OldName As String, ByVal NewName As String) As Boolean
Dim tStr As String
tStr = ActiveWorkbook.Names(OldName).RefersTo
ActiveWorkbook.Names(OldName).Delete
ActiveWorkbook.Names.Add NewName, tStr
End Function
'call using ->RenameNamedRange "rng1", "rng2"

Matt

ilyaskazi
10-07-2005, 07:08 AM
Great. You are incredibly smart!!
thankyou:thumb