Control Deletion of Range Names

[Alex Hatzisavas]

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

 

[Norman Jones]

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