Removing named cells

  • Thread starter Thread starter greg
  • Start date Start date
G

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
 
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.
 
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
 
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
 
Back
Top