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

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" <(E-Mail Removed)> wrote in message
news:B5E50064-5AA2-45D7-8AC4-(E-Mail Removed)...
>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

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

Doug

"ranswrt" <(E-Mail Removed)> wrote in message
news:B5E50064-5AA2-45D7-8AC4-(E-Mail Removed)...
>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

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" <(E-Mail Removed)> wrote in message
> news:B5E50064-5AA2-45D7-8AC4-(E-Mail Removed)...
> >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
>
>
>

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 here:

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

Doug

"ranswrt" <(E-Mail Removed)> wrote in message
news:A51E992C-7C9B-441F-B5CE-(E-Mail Removed)...
>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" <(E-Mail Removed)> wrote in message
>> news:B5E50064-5AA2-45D7-8AC4-(E-Mail Removed)...
>> >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
>>
>>
>>

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 here:
>
> http://www.jkp-ads.com/officemarketplacenm-en.asp
>
> Doug
>
> "ranswrt" <(E-Mail Removed)> wrote in message
> news:A51E992C-7C9B-441F-B5CE-(E-Mail Removed)...
> >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" <(E-Mail Removed)> wrote in message
> >> news:B5E50064-5AA2-45D7-8AC4-(E-Mail Removed)...
> >> >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
> >>
> >>
> >>
>
>
>