Deleting Named Ranges

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

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.

--
Regards,
Tom Ogilvy


"Grant Reid" <grantr@sybase.co.za> wrote in message
news:%23ybjWk2wFHA.2232@TK2MSFTNGP11.phx.gbl...
> 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
>
>

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" <grantr@sybase.co.za> wrote in message
news:%23ybjWk2wFHA.2232@TK2MSFTNGP11.phx.gbl...
> 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
>
>

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

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.

--
Regards,
Tom Ogilvy

"Grant Reid" <grantr@sybase.co.za> wrote in message
news:u11zJ22wFHA.3892@TK2MSFTNGP12.phx.gbl...
> 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
>
>

Hi

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

Kind Regards - Grant