deleting named cells

[ranswrt]

I have the following code to delete named cells for a worksheet:

With Sheets(nme & " DB")
.Names(nme & "cst1").Delete
.Names(nme & "cst2").Delete
.Names(nme & "cst3").Delete
.Names(nme & "cst4").Delete
.Names(nme & "cst5").Delete
.Names(nme & "date").Delete
.Names(nme & "daterng").Delete
.Names(nme & "item").Delete
.Names(nme & "itemno").Delete
.Names(nme & "itemnum").Delete
.Names(nme & "tax").Delete
.Names(nme & "slct").Delete
.Names(nme & "subven").Delete
.Names(nme & "subvenrng").Delete
.Names(nme & "unit").Delete
.Names(nme & "no").Delete
.Names(nme & "norng").Delete
End With

I get and error with this. I think I am not starting the 'with' statement
right. Is there a better way to do this?
Thanks

 

[Gary Keramidas]

this should work if you want to delete all of the names in the workbook



Sub test()

Dim nm As Name

Select Case MsgBox("Are you Sure You Want To Delete All Named Ranges?", _
vbOKCancel Or vbExclamation Or vbDefaultButton1, Application.Name)
Case vbOK
For Each nm In ThisWorkbook.Names
nm.Delete
Next nm
Case vbCancel
Exit Sub
End Select
End Sub

 

[Doug Glancy]

Are you sure they are worksheet, not workbook, level names? It works fine
for me with worksheet level names.

Doug

 

[ranswrt]

I change it to activeworkbook and that worked. What makes it an worksheet
level or workbook level?

 

[Doug Glancy]

Here's a link that explains it:
http://www.bettersolutions.com/excel/EFC133/VV415156111.htm

If you find yourself working with names at all you should really use Name
Manager, a stupendous addin found here:

http://www.jkp-ads.com/officemarketplacenm-en.asp

Doug

 

[ranswrt]

Thanks I'll have a look at that

Here's a link that explains it:
http://www.bettersolutions.com/excel/EFC133/VV415156111.htm

If you find yourself working with names at all you should really use Name
Manager, a stupendous addin found here:

http://www.jkp-ads.com/officemarketplacenm-en.asp

Doug