I need this macro todo what it does but exclude the name range "Control1"

[tonywatsonhelp]

Hi Everyone,

I have this macro that removes all name ranges from a document,
it works great

but I would like to keep one called "Control1"

can anyone edit it so "Control1" is not removed

Thanks

Tony

[COLOR=#000000][FONT=verdana]Option Explicit 
 
Sub RemNamedRanges() 
     
    Dim nm              As Name 
     
    On Error Resume Next 
    For Each nm In ActiveWorkbook.Names 
        nm.Delete 
    Next 
    On Error Goto 0 
     
End Sub [/FONT][/COLOR]

 

[Roderick_E]

Hi Everyone,

I have this macro that removes all name ranges from a document,
it works great

but I would like to keep one called "Control1"

can anyone edit it so "Control1" is not removed

Thanks

Tony

[COLOR=#000000][FONT=verdana]Option Explicit 
 
Sub RemNamedRanges() 
     
    Dim nm              As Name 
     
    On Error Resume Next 
    For Each nm In ActiveWorkbook.Names 
[/FONT][/COLOR][COLOR=#ff0000][FONT=verdana]if nm.name<>"Control1" then[/FONT][/COLOR][COLOR=#000000][FONT=verdana]
        nm.Delete 
[/FONT][/COLOR][COLOR=#ff0000][FONT=verdana]endif[/FONT][/COLOR][COLOR=#000000][FONT=verdana]
    Next 
    On Error Goto 0 
     
End Sub [/FONT][/COLOR]

How's that?

 

[mumps]

Maybe:

Option Explicit
Sub RemNamedRanges()
    Dim nm As Name
    On Error Resume Next
    For Each nm In ActiveWorkbook.Names
        If nm <> "Control1" Then
            nm.Delete
        End If
    Next
    On Error GoTo 0
End Sub

 

[tonywatsonhelp]

Thank you again Roderrick and thank you Mumps,

Exactly what I needed

Thanks

Tony

 

[mumps]

Glad to help.

 

[Joe4]

Mumps, your code will not work correctly. It is missing something.
This:

If nm <> "Control1" Then

needs to look like this:

If nm[COLOR=#ff0000].Name[/COLOR] <> "Control1" Then

like in Roderick's code.

Otherwise it will delete all named ranges!

 

[mumps]

@Joe4: Thank you. Should have picked up on that.

 

[Joe4]

You are welcome.

Just wanted to make sure that someone doesn't pick up the wrong code and inadvertantly delete something they didn't intend to!