Control Deletion of Range Names
 

Cheers all!

I want to perform the following simple task:

Check all the Range Names in a certain Workbook, and delete some o
them according to certain criteria (e.g. delete a Range Name if it
first 3 characters are XXX).

I'm using the following Sub, which doesn't work:

Sub DELETE_SOME_RANGE_NAMES()

Dim nm As Name

For Each nm In ActiveWorkbook.Names
If Left(nm, 3) = "XXX" Then
nm.Delete
End If
Next

End Sub

Using a Msgbox to check what values the Object Variable nm is gettin
(MsgBox = "nm = " & nm), I noted that its value is:
= << Sheet or Location ! << Address

Obviously, I would rather have Variable nm as the actual name o
whatever Range in order to control a string (the actual name) rathe
than a location & address...

Is there any way around this?

Thank you very much for your time,

Ale

--
Alex Hatzisava
-----------------------------------------------------------------------
Alex Hatzisavas's Profile: http://www.excelforum.com/member.php...fo&userid=1455
View this thread: http://www.excelforum.com/showthread.php?threadid=26229

Control Deletion of Range Names
 

Hi Alex,

Try:

Sub DELETE_SOME_RANGE_NAMES()
Dim nm As Name
Dim pos as Long
Dim sStr As String
For Each nm In ActiveWorkbook.Names
pos = InStr(nm.Name, "!") + 1
sStr = Mid(nm.Name, pos, 255)
If Left(sStr, 3) = "XXX" Then
nm.Delete
End If
Next
End Sub


---
Regards,
Norman



"Alex Hatzisavas" wrote in
message ...

Cheers all!

I want to perform the following simple task:

Check all the Range Names in a certain Workbook, and delete some of
them according to certain criteria (e.g. delete a Range Name if its
first 3 characters are XXX).

I'm using the following Sub, which doesn't work:

Sub DELETE_SOME_RANGE_NAMES()

Dim nm As Name

For Each nm In ActiveWorkbook.Names
If Left(nm, 3) = "XXX" Then
nm.Delete
End If
Next

End Sub

Using a Msgbox to check what values the Object Variable nm is getting
(MsgBox = "nm = " & nm), I noted that its value is:
= << Sheet or Location ! << Address

Obviously, I would rather have Variable nm as the actual name of
whatever Range in order to control a string (the actual name) rather
than a location & address...

Is there any way around this?

Thank you very much for your time,

Alex


--
Alex Hatzisavas
------------------------------------------------------------------------
Alex Hatzisavas's Profile:
http://www.excelforum.com/member.php...o&userid=14559
View this thread: http://www.excelforum.com/showthread...hreadid=262297