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
Powered by vBulletin® Version 4.2.5 Copyright © 2024 vBulletin Solutions Inc. All rights reserved.