 |
 | Home | Contact Us | FAQ | Search & Site Map | Link to Us |
 | Sign In | Join | Other 45 Sites in Network |
| HomeDiscussionsAccessExcelInfoPathOutlookPowerPointPublisherWordDirectoryUser Groups Related Topics Outlook ExpressInternet ExplorerWindowsMS Server ProductsMore Topics ... |
MS Office Forum / Excel / General Excel Questions / May 2008How to delete names on worksheet?
I have many names inherited from copying worksheet from external files, the file now contains hundreds of names that is giving me some problems. Apart from deleting one at a time using 'Insert'-'Name'-'Define', wonder anybody know how I can delete all the names completely. Press Alt+F11 to open the VB editor. In the VB Editor click on INSERT in the menu and select MODULE In your new module enter the following macro: Sub ClearNames() Dim wb As Workbook Dim n As Name For Each n In ThisWorkbook.Names n.Delete Next n Set n = Nothing End Sub Save the macro by click on FILE in the menu and selecting SAVE Press Alt + Q to return to the workbook or click FILE in the menu and select CLOSE AND RETURN... From the workbook press Alt + F8, select the macro named ClearNames and click the RUN button. Hope this helps...  SignatureKevin Backmann > I have many names inherited from copying worksheet from external files, the > file now contains hundreds of names that is giving me some problems. Apart > from deleting one at a time using 'Insert'-'Name'-'Define', wonder anybody > know how I can delete all the names completely. Thanks for your help. I tried, following your instruction but it resulted in a "run-time error '1004'. that name is not valid.". When I debug, it highlighted "n.Delete". > Press Alt+F11 to open the VB editor. > [quoted text clipped - 29 lines] > > from deleting one at a time using 'Insert'-'Name'-'Define', wonder anybody > > know how I can delete all the names completely. Did you not see these answers to your duplicate post from a few days ago? http://groups.google.com/group/microsoft.public.excel.misc/browse_frm/thread/600 168fe4496b28c/4d36d0964e4e333f?lnk=st&q=#4d36d0964e4e333f Pete > I have many names inherited from copying worksheet from external files, the > file now contains hundreds of names that is giving me some problems. Apart > from deleting one at a time using 'Insert'-'Name'-'Define', wonder anybody > know how I can delete all the names completely. Saw it now. Thanks. > Did you not see these answers to your duplicate post from a few days > ago? [quoted text clipped - 7 lines] > > from deleting one at a time using 'Insert'-'Name'-'Define', wonder anybody > > know how I can delete all the names completely. I followed your instruction but it resulted in an error "run-time error '1004': that name is not valid." > Did you not see these answers to your duplicate post from a few days > ago? [quoted text clipped - 7 lines] > > from deleting one at a time using 'Insert'-'Name'-'Define', wonder anybody > > know how I can delete all the names completely. Just check that you have typed everything in correctly, including the spaces. Hope this helps. Pete > I followed your instruction but it resulted in an error "run-time error > '1004': that name is not valid." I copied your instruction over, so shouldn't have missed out anything. > Just check that you have typed everything in correctly, including the > spaces. [quoted text clipped - 5 lines] > > I followed your instruction but it resulted in an error "run-time error > > '1004': that name is not valid." Okay then, I've just done this in a workbook with named ranges. Try it like this: For Each nme In ActiveWorkbook.Names : nme.Delete : Next nme Copy this into the immediate window, then press <enter> - it worked for me. Hope this helps. Pete > I copied your instruction over, so shouldn't have missed out anything. > [quoted text clipped - 9 lines] > > - Show quoted text - I'd use Jan Karel Pieterse's (with Charles Williams and Matthew Henson) Name Manager: You can find it at: NameManager.Zip from http://www.oaltd.co.uk/mvp You may want to keep some of the names that you defined -- or keep the names that excel uses that you didn't know even existed. > I have many names inherited from copying worksheet from external files, the > file now contains hundreds of names that is giving me some problems. Apart > from deleting one at a time using 'Insert'-'Name'-'Define', wonder anybody > know how I can delete all the names completely. SignatureDave Peterson |
Reply to this Message |
 Sign In
 Join
 My Latest Posts My Monitored Threads My Blog My Photo Gallery My Profile My Homepage Start New Thread Enable EMail Alerts Rate this Thread
|
©2008 Advenet LLC Privacy Policy - Terms of Use
This website includes both content owned or controlled by Advenet as well as content owned or controlled by third parties.