Local names remain when sheet is deleted

bk1

New Member
Joined
Aug 7, 2009
Messages
12
I have a really weird problem. I have a lot of local names on many of my sheets in my workbook. The sheets are deleted by VBA macros.

Now, for some of my sheets the local names are converted to global names and the sheet name becomes REF#. Is there some kind of logic behind when this happens, because I cannot seem to find it. Which names are staying behind seem to depend on something I cannot deduce.
 

Excel Facts

Round to nearest half hour?
Use =MROUND(A2,"0:30") to round to nearest half hour. Use =CEILING(A2,"0:30") to round to next half hour.

Derek Brown

Well-known Member
Joined
Dec 26, 2005
Messages
2,390
Hello and welcome to The Board.
I think that the problem is as a result of the 'scope' of the "Name" (e.g. workbook or worksheet).
It is easier to see the scope in the Excel 2007 Name Manager.
If it is 'worksheet' scope then it will be removed from Name Manager list when the worksheet is deleted. For 'workbook' scope it appears to remain but because the sheet to which it refers has been deleted, then it gives #REF for the worksheet name.
You could run code to remove invalid names but always make a backup copy of a workbook before making a big change (just in case!).
Something like the following - add a prompt to confirm deletion if you want to.
Code:
Option Explicit
Sub Delete_Invalid_Named_Formulas()
Dim oName As Object
For Each oName In ActiveWorkbook.Names
    If InStr(1, oName.RefersTo, "#REF") > 0 Then
        oName.Delete
    End If
Next
Set oName = Nothing
End Sub
 

bk1

New Member
Joined
Aug 7, 2009
Messages
12
Well, yes this is what I thought was going on at first, but that was not the case. It is indeed local names with a specific worksheet scope that is being converted - sometimes.
 

Forum statistics

Threads
1,136,447
Messages
5,675,903
Members
419,591
Latest member
mersanko

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Top