Deleting Named Ranges

[Grant Reid]

Hi

I hope someone can help me ou there. I'm trying to delete all named ranges
in all Worksheets, except one named "Connection Data", but I don't seem to
having any success. I have pasted my code below. If anyone can enlighten me
as to where I'm going wrong, it would be much appreciated.

Many Thanks - Grant

Sub DeleteRanges()
Dim WSh As Worksheet
Dim nm As Name

'----------------------------
'Delete All Named Ranges Except Named Ranges "In Connection Data" Workbook
'----------------------------

For Each WSh In ThisWorkbook.Worksheets
If Not WSh.Name Like "Connection Data" Then
For Each nm In ActiveWorkbook.Names
nm.Delete
Next nm
End If
Next WSh

End Sub

 

[Tom Ogilvy]

For Each nm In ActiveWorkbook.Names
if instr(1,nm.Refersto,"Connection Data",vbTextCompare) = 0 then
nm.Delete
end if
Next nm

Might be what you want.

 

[Bob L.]

I believe your if statement is misplaced and you name the name property to
get the name and not the address. Try this:

Sub DeleteRanges()
Dim WSh As Worksheet
Dim nm As Name

'----------------------------
'Delete All Named Ranges Except Named Ranges "In Connection Data" Workbook
'----------------------------

For Each WSh In ThisWorkbook.Worksheets

For Each nm In ActiveWorkbook.Names

If Not nm.Name Like "Connection Data" Then

nm.Delete
End If
Next nm

Next WSh

End Sub



Bob L.

 

[Grant Reid]

Hi Tom

Thanks for your response. Its not quite what I need though, perhaps I
couched my question incorrectly. What I actually am attempting to do is to
loop through all Worksheets, except "Connection Data" and delete all named
ranges. So utlimately, the only named ranges remaining will be the named
ranges in the Worksheet "Connection Data".

Many Thanks - Grant

 

[Tom Ogilvy]

You have to define what you mean by named ranges in the Worksheet
"Connection Data".

If you mean named ranges that refer to ranges in the worksheet "Connection
Data", then I gave you a solution.

If you mean named ranges that are utilized in formulas found in the
worksheet "Connection Data", then you have much more work to do.

If you are talking about sheet level names, then you shouldn't be looping in
the ActiveWorkbook.Names collection.

 

[Grant Reid]

Hi

Many thanks to you Tom and Bob. I managed to figure it out myself - its been
a loooong day.

Kind Regards - Grant