Deleting Defined Names

pvr928

Well-known Member
Joined
Oct 21, 2002
Messages
790
Does anyone know of a macro that will delete Defined Names from a workbook, rather than going in and deleting each Name from the Insert-Name-Define function?

Cheers

pvr928
 

Excel Facts

Excel Can Read to You
Customize Quick Access Toolbar. From All Commands, add Speak Cells or Speak Cells on Enter to QAT. Select cells. Press Speak Cells.

nbrcrunch

Well-known Member
Joined
Jan 1, 2003
Messages
2,062
Have you tried recording a macro while deleting just one Named Range and then modifying it?
 

pvr928

Well-known Member
Joined
Oct 21, 2002
Messages
790
nbrcrunch

Yes, but it doesn't help with the structure of a macro to delete a large number of Names.

I have found this macro on the Board:

Sub RemoveNames()

Dim MyName, Response

For Each MyName In ThisWorkbook.Names

Response = MsgBox("Delete " & MyName.Name & "Refers To: " & MyName.RefersTo & "?", vbYesNo)

If Response = vbYes Then MyName.Name.Delete

Next MyName

End Sub

but it is coming up with a 424 run time error, requiring an Object for the 'MyName.Name.Delete' function.

Any help greatly appreciated.

cheers

pvr928
 

Yogi Anand

MrExcel MVP
Joined
Mar 12, 2002
Messages
11,454
Hi pvr928:

Try the following ...
Code:
Sub RemoveNames()
    Dim MyName, Response
        For Each MyName In ThisWorkbook.Names
            Response = MsgBox("Delete " & MyName.Name & "Refers To: " & MyName.RefersTo & "?", vbYesNo)
            'Yogi Anand: If Response = vbYes Then MyName.Name.Delete ... doesn't work for me in EXCEL 97 ... use the following line instead
            If Response = vbYes Then MyName.Delete
        Next MyName
End Sub
 

pvr928

Well-known Member
Joined
Oct 21, 2002
Messages
790
Thanks Yogi & Aladin.

Yogi - I got either a run time or property error depending on which of the MyName.Delete or MyName.Name.Delete I used - any suggestions?

Aladin - the Name Manager looks awesome!! - but my system won't let me download the add-in.

Cheers

pvr928
 

Richard Schollar

MrExcel MVP
Joined
Apr 19, 2005
Messages
23,707
Yogi's code:

Code:
Sub RemoveNames()
    Dim MyName, Response
        For Each MyName In ThisWorkbook.Names
            Response = MsgBox("Delete " & MyName.Name & "Refers To: " & MyName.RefersTo & "?", vbYesNo)
            
            If Response = vbYes Then MyName.Delete
        Next MyName
End Sub

works perfectly for me - if you copy the above code and insert it into your module and run it does it still give you the error?

What line of code does it stop on?

Best regards

Richard
 

pvr928

Well-known Member
Joined
Oct 21, 2002
Messages
790
Parsnip

It stops on the 'MyName.Delete' function.

I think I have figured out why it does that - it is because the Name it is trying to delete has a 'REF#!" error in it.

I do have a macro that deletes all Names that have a 'REF#!" error in them - but this remaining Name is a ghost link that I can't seem to track down - it appears to be connected with a chart, but I cannot for the life of me find it in the Workbook.

If the Name does not have an error in it, the macro does work as intended.

I'd appreciate any assistance in tracking down this ghost link.

Cheers

pvr928
 
Master Excel Bundle

Excel contains over 450 functions, with more added every year. That’s a huge number, so where should you start? Right here with this bundle.

Forum statistics

Threads
1,164,552
Messages
5,838,063
Members
430,527
Latest member
MyFace2

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Top