How to delete multiple defined names in excel 2003

[Deadpool]

how to delete multiple defined names in excel 2003. I came across this macro to do so but it is giving me a run time error. Not sure what is wrong with it. I used a excel spreadsheet from a friend that had a bunch of named cells and I don't know how to remove all of them. Please help



Sub DeleteAllNamesWithREFError()

Dim N As Name

If MsgBox("Are you sure?", vbYesNo + vbDefaultButton2, "Confirm macro") = vbNo Then Exit Sub

For Each N In ActiveWorkbook.Names

If InStr(N.Value, "#REF") Then N.Delete

Next N

End Sub

 

[Domenic]

As it stands, your code only deletes names that contain the error value #REF ! To delete all names, whether or not they include the error value, replace...

If InStr(N.Value, "[URL=https://www.mrexcel.com/forum/usertag.php?do=list&action=hash&hash=REF]#REF[/URL] ") Then N.Delete

with

N.Delete

Hope this helps!

 

[Deadpool]

As it stands, your code only deletes names that contain the error value #REF ! To delete all names, whether or not they include the error value, replace...

If InStr(N.Value, "[URL="https://www.mrexcel.com/forum/usertag.php?do=list&action=hash&hash=REF"]#REF[/URL] ") Then N.Delete

with

N.Delete

Hope this helps!

Thank you for the help. So I tried this and it went to the debug. Is this correct?

Sub DeleteAllNamesWithREFError()

Dim N As Name

If MsgBox("Are you sure?", vbYesNo + vbDefaultButton2, "Confirm macro") = vbNo Then Exit Sub

For Each N In ActiveWorkbook.Names

Then N.Delete

Next N

End Sub

 

[Domenic]

No, it should be this way...

Sub DeleteAllNames()

    Dim N As Name
    
    If MsgBox("Are you sure?", vbYesNo + vbDefaultButton2, "Confirm macro") = vbNo Then Exit Sub
    
    For Each N In ActiveWorkbook.Names
    
        N.Delete
    
    Next N


End Sub

You'll notice that I took the liberty to change the name of the macro to DeleteAllNames, since you in fact want to delete all names.

 

[Deadpool]

No, it should be this way...

Sub DeleteAllNames()

    Dim N As Name
    
    If MsgBox("Are you sure?", vbYesNo + vbDefaultButton2, "Confirm macro") = vbNo Then Exit Sub
    
    For Each N In ActiveWorkbook.Names
    
        N.Delete
    
    Next N


End Sub

You'll notice that I took the liberty to change the name of the macro to DeleteAllNames, since you in fact want to delete all names.

Thank you so much that worked have a great day.