deleting named cells
 

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

deleting named cells
 

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


--


Gary


"ranswrt" wrote in message
...
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

deleting named cells
 

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

Doug

"ranswrt" wrote in message
...
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

deleting named cells
 

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

"Doug Glancy" wrote:

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

Doug

"ranswrt" wrote in message
...
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

deleting named cells
 

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

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

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

Doug

"ranswrt" wrote in message
...
I change it to activeworkbook and that worked. What makes it an worksheet
level or workbook level?

"Doug Glancy" wrote:

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

Doug

"ranswrt" wrote in message
...
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

deleting named cells
 

Thanks I'll have a look at that

"Doug Glancy" wrote:

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

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

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

Doug

"ranswrt" wrote in message
...
I change it to activeworkbook and that worked. What makes it an worksheet
level or workbook level?

"Doug Glancy" wrote:

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

Doug

"ranswrt" wrote in message
...
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