Removing named cells

[greg]

I create and delete lots of cells in one of my excel applications. Each
cell has a named cell.
I have just noticed that I have a bunch of "bad"/"unreferenced" named cells.
I have a bunch of these
=Sheet1!#REF!

Is there a way I can go in and revove all of these programmatically?
Can you find named cells by REF?
thanks

 

[JLGWhiz]

You could probably delete them manually by the time you developed the code to
do it. Insert>Name>define will bring up the listbox that has all of your
named ranges/cells so you can just pick the ones you want to delete and click
delete.

 

[john]

this may work (not tested)



Sub clearnames()
For Each rangename In ActiveWorkbook.Names
msg = MsgBox("Range Name: " & rangename.Name & Chr(10) & _
" REFERS TO: " & rangename & Chr(10) & Chr(10) & _
" Delete Name?", 36, "Delete Name")
If msg = 6 Then rangename.Delete
Next rangename
End Sub

 

[Gary Keramidas]

see if this does what you want:

Sub name_ranges2()
Dim nm As Name

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

End Sub

 

[Gary Keramidas]

missed a character that may make it safer:

If nm Like "*REF!*" Then

 

[greg]

thanks, I will give it a try

missed a character that may make it safer:

If nm Like "*REF!*" Then