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. InsertNamedefine 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.

"greg" wrote:

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

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

--
jb


"greg" wrote:

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

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


"greg" wrote in message
...
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

missed a character that may make it safer:

If nm Like "*REF!*" Then
--


Gary


"Gary Keramidas" <GKeramidasATmsn.com wrote in message
...
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


"greg" wrote in message
...
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

thanks, I will give it a try


"Gary Keramidas" <GKeramidasATmsn.com wrote in message
...

missed a character that may make it safer:

If nm Like "*REF!*" Then
--


Gary


"Gary Keramidas" <GKeramidasATmsn.com wrote in message
...
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


"greg" wrote in message
...
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