I have a procedure to delete a sheet in a workbook. All the named cells in
that sheet gets deleted. When I use the 'F3' to paste the list of named
cells, the cells in the sheet that was deleted appear in it. Do I need to
delete the named cells before I delete the sheet?

Thanks

It looks like that is the only way to get them to disappear from the "F3"
list.

"ranswrt" wrote:

> I have a procedure to delete a sheet in a workbook. All the named cells in
> that sheet gets deleted. When I use the 'F3' to paste the list of named
> cells, the cells in the sheet that was deleted appear in it. Do I need to
> delete the named cells before I delete the sheet?
>
> Thanks

Is there a problem to not delete the named cells berfore a sheet is deleted?

"Mike H." wrote:

> It looks like that is the only way to get them to disappear from the "F3"
> list.
>
> "ranswrt" wrote:
>
> > I have a procedure to delete a sheet in a workbook. All the named cells in
> > that sheet gets deleted. When I use the 'F3' to paste the list of named
> > cells, the cells in the sheet that was deleted appear in it. Do I need to
> > delete the named cells before I delete the sheet?
> >
> > Thanks

I can not think of a single reason why there would be a problem. The named
ranges no longer appear when you hit F5, the Goto Range Key and the F3 key
seems to be the only place that "keeps" them. So I don't think it is a
problem.

"ranswrt" wrote:

> Is there a problem to not delete the named cells berfore a sheet is deleted?
>
> "Mike H." wrote:
>
> > It looks like that is the only way to get them to disappear from the "F3"
> > list.
> >
> > "ranswrt" wrote:
> >
> > > I have a procedure to delete a sheet in a workbook. All the named cells in
> > > that sheet gets deleted. When I use the 'F3' to paste the list of named
> > > cells, the cells in the sheet that was deleted appear in it. Do I need to
> > > delete the named cells before I delete the sheet?
> > >
> > > Thanks

You could delete the names before or after you delete the worksheet--or you
could let those invalid names just stick around.

If you're working with names, get 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'll find it very valuable.



ranswrt wrote:
>
> I have a procedure to delete a sheet in a workbook. All the named cells in
> that sheet gets deleted. When I use the 'F3' to paste the list of named
> cells, the cells in the sheet that was deleted appear in it. Do I need to
> delete the named cells before I delete the sheet?
>
> Thanks

--

Dave Peterson

When you create a name in a worksheet, it creates it for the workbook. That
is why is is still
there after you delete the sheet. However if you want to get rid of it you
can go to the name manager
and select it and the delete icon will become active. Click on the delete
and it is gone.
Skinman



"ranswrt" <(E-Mail Removed)> wrote in message
news:E9A7FCCD-74F3-46E0-A6C1-(E-Mail Removed)...
> Is there a problem to not delete the named cells berfore a sheet is
> deleted?
>
> "Mike H." wrote:
>
>> It looks like that is the only way to get them to disappear from the "F3"
>> list.
>>
>> "ranswrt" wrote:
>>
>> > I have a procedure to delete a sheet in a workbook. All the named
>> > cells in
>> > that sheet gets deleted. When I use the 'F3' to paste the list of
>> > named
>> > cells, the cells in the sheet that was deleted appear in it. Do I need
>> > to
>> > delete the named cells before I delete the sheet?
>> >
>> > Thanks

ps.

You may want to start using local/worksheet names instead of global/workbook
names.

ranswrt wrote:
>
> I have a procedure to delete a sheet in a workbook. All the named cells in
> that sheet gets deleted. When I use the 'F3' to paste the list of named
> cells, the cells in the sheet that was deleted appear in it. Do I need to
> delete the named cells before I delete the sheet?
>
> Thanks

--

Dave Peterson

Thanks for your help

"Dave Peterson" wrote:

> ps.
>
> You may want to start using local/worksheet names instead of global/workbook
> names.
>
> ranswrt wrote:
> >
> > I have a procedure to delete a sheet in a workbook. All the named cells in
> > that sheet gets deleted. When I use the 'F3' to paste the list of named
> > cells, the cells in the sheet that was deleted appear in it. Do I need to
> > delete the named cells before I delete the sheet?
> >
> > Thanks
>
> --
>
> Dave Peterson
>

You could use code like this to delete your worksheet and any Names
referencing it...

Sub DeleteSheetAndItsNames()
Dim N As Name
Dim WS As String
WS = "Sheet3"
For Each N In ThisWorkbook.Names
If Mid(Split(N.RefersTo, "!")(0), 2) = WS Then N.Delete
Next
Worksheets(WS).Delete
End Sub

Just change the worksheet name reference or, better still maybe, make it a
Variant argument to the subroutine (then you can specify the worksheet name
or number) so that your code can call it and specify the worksheet name or
number when doing so. You can then, of course, bolster it up with some error
checking.

Rick


"ranswrt" <(E-Mail Removed)> wrote in message
news:4A99B6CB-DC28-4ECB-B5E4-(E-Mail Removed)...
>I have a procedure to delete a sheet in a workbook. All the named cells in
> that sheet gets deleted. When I use the 'F3' to paste the list of named
> cells, the cells in the sheet that was deleted appear in it. Do I need to
> delete the named cells before I delete the sheet?
>
> Thanks

Well, actually, if you changed it to a Variant argument, you would have to
adapt the If-Then statement to account for that. Something like this
maybe...

Sub DeleteSheetAndItsNames(WS As Variant)
Dim N As Name
Dim S As Worksheet
If WS Like String(Len(WS), "#") Then
If WS <= Worksheets.Count Then
WS = Worksheets(WS).Name
End If
End If
For Each S In Worksheets
If S.Name = WS Then
For Each N In ThisWorkbook.Names
If Mid(Split(N.RefersTo, "!")(0), 2) = WS Then N.Delete
Next
Worksheets(WS).Delete
Exit Sub
End If
Next
MsgBox "No such worksheet!", vbCritical, "No Such Worksheet"
Err.Raise 1111, , "No such worksheet!"
End Sub

I provided a trappable error that can be caught by your calling code. For
example...

Sub Test()
On Error GoTo Whoops
DeleteSheetAndItsNames "SheetX"
Exit Sub
Whoops:
Debug.Print Err.Number, Err.Description
End Sub

Rick


"Rick Rothstein (MVP - VB)" <(E-Mail Removed)> wrote in
message news:(E-Mail Removed)...
> You could use code like this to delete your worksheet and any Names
> referencing it...
>
> Sub DeleteSheetAndItsNames()
> Dim N As Name
> Dim WS As String
> WS = "Sheet3"
> For Each N In ThisWorkbook.Names
> If Mid(Split(N.RefersTo, "!")(0), 2) = WS Then N.Delete
> Next
> Worksheets(WS).Delete
> End Sub
>
> Just change the worksheet name reference or, better still maybe, make it a
> Variant argument to the subroutine (then you can specify the worksheet
> name or number) so that your code can call it and specify the worksheet
> name or number when doing so. You can then, of course, bolster it up with
> some error checking.
>
> Rick
>
>
> "ranswrt" <(E-Mail Removed)> wrote in message
> news:4A99B6CB-DC28-4ECB-B5E4-(E-Mail Removed)...
>>I have a procedure to delete a sheet in a workbook. All the named cells
>>in
>> that sheet gets deleted. When I use the 'F3' to paste the list of named
>> cells, the cells in the sheet that was deleted appear in it. Do I need
>> to
>> delete the named cells before I delete the sheet?
>>
>> Thanks
>