Rename my range name

[ilyaskazi]

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]

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]

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]

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]

Great. You are incredibly smart!!
thankyou