Defined Names (Excel 2003)

Topic

New Reply

Is there a way to delete defined name list?

Reply | Quote

Replies

If you have Jan Karel Pieterse’s Name Manager add-in, you can select and delete them all in one go. Otherwise you would need code:

Code:

Sub DeleteNames()

Application.ExecuteExcel4Macro "SUM(DELETE.NAME(NAMES()))"
End Sub

for example.

Reply | Quote

If you have Jan Karel Pieterse’s Name Manager add-in, you can select and delete them all in one go. Otherwise you would need code:

Code:

Sub DeleteNames()

Application.ExecuteExcel4Macro "SUM(DELETE.NAME(NAMES()))"
End Sub

for example.

I thought excel use a common list of defined name. I have deleted the list from one wb with help of code provided, but it is still there in another wbs. what to do?

Reply | Quote

I thought excel use a common list of defined name.

No – they are workbook specific. You would have to run the code on each workbook.

Reply | Quote

No – they are workbook specific. You would have to run the code on each workbook.

That way, it is better to keep them alive. It will take me a day to delete them from each workbook. Thanks Rory for help.

Reply | Quote

No – they are workbook specific. You would have to run the code on each workbook.

Is it, any how, possible to delete the defined name list from entire workbook having more than 1 sheet? The code provided delete list from selected sheet only.

Reply | Quote

Try this:

Code:

Sub DeleteNames()
   Dim nm as Name
   For each nm in Activeworkbook.Names
	  nm.Delete
   Next nm
End Sub

Reply | Quote

Try this:

Code:

Sub DeleteNames()
   Dim nm as Name
   For each nm in Activeworkbook.Names
	  nm.Delete
   Next nm
End Sub

It is not working properly. Keep some names un-deleted & a run time erron occured. On dubging, it highlights the “nm.Delete” syntex.

Reply | Quote

Sounds to me as though you may have some corrupt names in there. What names do you have left in the dialog?

Reply | Quote

Sounds to me as though you may have some corrupt names in there. What names do you have left in the dialog?

You are absolutely right Rory. Most of them are corrupted as they reffered to un-identified locations/sheets and no more in use.

Reply | Quote

n5 is not a valid range name as it’s a cell reference. I suggest you switch to R1C1 style referencing via the Tools-Options dialog, then run the code gain, then switch back to A1-style.

Reply | Quote

n5 is not a valid range name as it’s a cell reference. I suggest you switch to R1C1 style referencing via the Tools-Options dialog, then run the code gain, then switch back to A1-style.

Yes, perfect. Thank you very much Rory, for spare your precious time.

Reply | Quote

If you have Jan Karel Pieterse’s Name Manager add-in, you can select and delete them all in one go

BTW, can I have the Name Manager add-in?

Reply | Quote

Yes, you can download it from here (it’s a must-have!)

Reply | Quote

Yes, you can download it from here (it’s a must-have!)

and one more

I have to go home and drinking with driving is not permited here.

Reply | Quote