Hello,
I use names a lot in a workbook. When a worksheet is deleted, the names
remain. I would like to delete those that are valid. My thought was to
cycle through the names and try to go to the name. If an error is
caused, then delete the name. I used the code below but every name is
deleted. What am I doing wrong or is there an easier way to do this.

Thanks

Bill

Sub RemoveNamesNotThere()
nm = ActiveWorkbook.Names.Count
On Error GoTo 0
For j = nm To 1 Step -1
On Error Resume Next
NN = ActiveWorkbook.Names(j)
Application.Goto Reference:=NN
If Err < 0 Then
ActiveWorkbook.Names(j).Delete
End If
On Error GoTo 0
Next j
CS.Activate
End Sub


*** Sent via Developersdex http://www.developersdex.com ***

i couldn't get the iserror to work for me

give this a try

Sub name_ranges3()
Dim nm As Name
On Error Resume Next
For Each nm In ThisWorkbook.Names
If Left(nm, 5) = "=#REF" Then
nm.Delete
End If
Next nm


End Sub


--


Gary


"Bill" wrote in message
...
Hello,
I use names a lot in a workbook. When a worksheet is deleted, the names
remain. I would like to delete those that are valid. My thought was to
cycle through the names and try to go to the name. If an error is
caused, then delete the name. I used the code below but every name is
deleted. What am I doing wrong or is there an easier way to do this.

Thanks

Bill

Sub RemoveNamesNotThere()
nm = ActiveWorkbook.Names.Count
On Error GoTo 0
For j = nm To 1 Step -1
On Error Resume Next
NN = ActiveWorkbook.Names(j)
Application.Goto Reference:=NN
If Err < 0 Then
ActiveWorkbook.Names(j).Delete
End If
On Error GoTo 0
Next j
CS.Activate
End Sub


*** Sent via Developersdex http://www.developersdex.com ***

try this,- delete all names

Sub DelNames()
Dim x
For Each x In ActiveWorkbook.Names
ActiveWorkbook.Names(x.Name).Delete
Next
End Sub

On Sat, 02 Sep 2006 08:05:05 -0700, Bill wrote:

I use names a lot in a workbook. When a worksheet is deleted, the names
remain. I would like to delete those that are valid.

Valid? I think you mean invalid. How about something like this?

Sub DeleteOldRangeNames()
Dim RN As Name
For Each RN In ActiveWorkbook.Names
If InStr(1, RN.RefersTo, "#REF", vbTextCompare) Then
RN.Delete
End If
Next RN
End Sub

Don <www.donwiss.com (e-mail link at home page bottom).

If you're developing a workbook (a one time thing), you may want to use 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

Heck, if you use names, this will make you're life easier.


Bill wrote:

Hello,
I use names a lot in a workbook. When a worksheet is deleted, the names
remain. I would like to delete those that are valid. My thought was to
cycle through the names and try to go to the name. If an error is
caused, then delete the name. I used the code below but every name is
deleted. What am I doing wrong or is there an easier way to do this.

Thanks

Bill

Sub RemoveNamesNotThere()
nm = ActiveWorkbook.Names.Count
On Error GoTo 0
For j = nm To 1 Step -1
On Error Resume Next
NN = ActiveWorkbook.Names(j)
Application.Goto Reference:=NN
If Err < 0 Then
ActiveWorkbook.Names(j).Delete
End If
On Error GoTo 0
Next j
CS.Activate
End Sub

*** Sent via Developersdex http://www.developersdex.com ***

--

Dave Peterson

Sub DeleteInvalidNames()

Dim oName As Name
Dim strAddress As String

On Error Resume Next

For Each oName In ActiveWorkbook.Names
strAddress = oName.RefersToRange.Address
If Len(strAddress) = 0 Then
oName.Delete
End If
Next

End Sub


RBS


"Bill" wrote in message
...
Hello,
I use names a lot in a workbook. When a worksheet is deleted, the names
remain. I would like to delete those that are valid. My thought was to
cycle through the names and try to go to the name. If an error is
caused, then delete the name. I used the code below but every name is
deleted. What am I doing wrong or is there an easier way to do this.

Thanks

Bill

Sub RemoveNamesNotThere()
nm = ActiveWorkbook.Names.Count
On Error GoTo 0
For j = nm To 1 Step -1
On Error Resume Next
NN = ActiveWorkbook.Names(j)
Application.Goto Reference:=NN
If Err < 0 Then
ActiveWorkbook.Names(j).Delete
End If
On Error GoTo 0
Next j
CS.Activate
End Sub


*** Sent via Developersdex http://www.developersdex.com ***

There are names that don't refer to ranges that will be deleted with this
routine.

RB Smissaert wrote:

Sub DeleteInvalidNames()

Dim oName As Name
Dim strAddress As String

On Error Resume Next

For Each oName In ActiveWorkbook.Names
strAddress = oName.RefersToRange.Address
If Len(strAddress) = 0 Then
oName.Delete
End If
Next

End Sub

RBS

"Bill" wrote in message
...
Hello,
I use names a lot in a workbook. When a worksheet is deleted, the names
remain. I would like to delete those that are valid. My thought was to
cycle through the names and try to go to the name. If an error is
caused, then delete the name. I used the code below but every name is
deleted. What am I doing wrong or is there an easier way to do this.

Thanks

Bill

Sub RemoveNamesNotThere()
nm = ActiveWorkbook.Names.Count
On Error GoTo 0
For j = nm To 1 Step -1
On Error Resume Next
NN = ActiveWorkbook.Names(j)
Application.Goto Reference:=NN
If Err < 0 Then
ActiveWorkbook.Names(j).Delete
End If
On Error GoTo 0
Next j
CS.Activate
End Sub


*** Sent via Developersdex http://www.developersdex.com ***

--

Dave Peterson

Yes, true, hadn't thought about that.

RBS

"Dave Peterson" wrote in message
...
There are names that don't refer to ranges that will be deleted with this
routine.

RB Smissaert wrote:

Sub DeleteInvalidNames()

Dim oName As Name
Dim strAddress As String

On Error Resume Next

For Each oName In ActiveWorkbook.Names
strAddress = oName.RefersToRange.Address
If Len(strAddress) = 0 Then
oName.Delete
End If
Next

End Sub

RBS

"Bill" wrote in message
...
Hello,
I use names a lot in a workbook. When a worksheet is deleted, the
names
remain. I would like to delete those that are valid. My thought was
to
cycle through the names and try to go to the name. If an error is
caused, then delete the name. I used the code below but every name is
deleted. What am I doing wrong or is there an easier way to do this.

Thanks

Bill

Sub RemoveNamesNotThere()
nm = ActiveWorkbook.Names.Count
On Error GoTo 0
For j = nm To 1 Step -1
On Error Resume Next
NN = ActiveWorkbook.Names(j)
Application.Goto Reference:=NN
If Err < 0 Then
ActiveWorkbook.Names(j).Delete
End If
On Error GoTo 0
Next j
CS.Activate
End Sub


*** Sent via Developersdex http://www.developersdex.com ***

--

Dave Peterson

That's one of the reasons I'll use Jan Karel (and others) addin. They have so
many things that I would have never thought about already built in.

The bad news is that sometimes you just have to have your own code clean up a
mess.

RB Smissaert wrote:

Yes, true, hadn't thought about that.

RBS

"Dave Peterson" wrote in message
...
There are names that don't refer to ranges that will be deleted with this
routine.

RB Smissaert wrote:

Sub DeleteInvalidNames()

Dim oName As Name
Dim strAddress As String

On Error Resume Next

For Each oName In ActiveWorkbook.Names
strAddress = oName.RefersToRange.Address
If Len(strAddress) = 0 Then
oName.Delete
End If
Next

End Sub

RBS

"Bill" wrote in message
...
Hello,
I use names a lot in a workbook. When a worksheet is deleted, the
names
remain. I would like to delete those that are valid. My thought was
to
cycle through the names and try to go to the name. If an error is
caused, then delete the name. I used the code below but every name is
deleted. What am I doing wrong or is there an easier way to do this.

Thanks

Bill

Sub RemoveNamesNotThere()
nm = ActiveWorkbook.Names.Count
On Error GoTo 0
For j = nm To 1 Step -1
On Error Resume Next
NN = ActiveWorkbook.Names(j)
Application.Goto Reference:=NN
If Err < 0 Then
ActiveWorkbook.Names(j).Delete
End If
On Error GoTo 0
Next j
CS.Activate
End Sub


*** Sent via Developersdex http://www.developersdex.com ***

--

Dave Peterson

--

Dave Peterson

Names can refer to multi-areas any one of which might be missing. To cater
suggest change -

If Left(nm, 5) = "=#REF" Then
to
If InStr(nm.RefersTo, "#REF!") Then

I posted a full demo in this ng
subject: "Delete broken named ranges in sheet"
date: 15 August 2005

but for some reason I cannot find it in Google

The best solution though is as referred to by Dave Peterson in this thread.

Regards,
Peter T

"GKeramidas" wrote in message
...
i couldn't get the iserror to work for me

give this a try

Sub name_ranges3()
Dim nm As Name
On Error Resume Next
For Each nm In ThisWorkbook.Names
If Left(nm, 5) = "=#REF" Then
nm.Delete
End If
Next nm


End Sub


--


Gary


"Bill" wrote in message
...
Hello,
I use names a lot in a workbook. When a worksheet is deleted, the names
remain. I would like to delete those that are valid. My thought was to
cycle through the names and try to go to the name. If an error is
caused, then delete the name. I used the code below but every name is
deleted. What am I doing wrong or is there an easier way to do this.

Thanks

Bill

Sub RemoveNamesNotThere()
nm = ActiveWorkbook.Names.Count
On Error GoTo 0
For j = nm To 1 Step -1
On Error Resume Next
NN = ActiveWorkbook.Names(j)
Application.Goto Reference:=NN
If Err < 0 Then
ActiveWorkbook.Names(j).Delete
End If
On Error GoTo 0
Next j
CS.Activate
End Sub


*** Sent via Developersdex http://www.developersdex.com ***

but for some reason I cannot find it in Google

Google Groups seems to have lost it, very strange. But Google finds it in
other places, eg

http://www.mailarchive.ca/lists/micr...ming/2005-08/5
120.html

Regards,
Peter T

"Peter T" <peter_t@discussions wrote in message
...
Names can refer to multi-areas any one of which might be missing. To cater
suggest change -

If Left(nm, 5) = "=#REF" Then
to
If InStr(nm.RefersTo, "#REF!") Then

I posted a full demo in this ng
subject: "Delete broken named ranges in sheet"
date: 15 August 2005

but for some reason I cannot find it in Google

The best solution though is as referred to by Dave Peterson in this
thread.

Regards,
Peter T

"GKeramidas" wrote in message
...
i couldn't get the iserror to work for me

give this a try

Sub name_ranges3()
Dim nm As Name
On Error Resume Next
For Each nm In ThisWorkbook.Names
If Left(nm, 5) = "=#REF" Then
nm.Delete
End If
Next nm


End Sub


--


Gary


"Bill" wrote in message
...
Hello,
I use names a lot in a workbook. When a worksheet is deleted, the
names
remain. I would like to delete those that are valid. My thought was
to
cycle through the names and try to go to the name. If an error is
caused, then delete the name. I used the code below but every name is
deleted. What am I doing wrong or is there an easier way to do this.

Thanks

Bill

Sub RemoveNamesNotThere()
nm = ActiveWorkbook.Names.Count
On Error GoTo 0
For j = nm To 1 Step -1
On Error Resume Next
NN = ActiveWorkbook.Names(j)
Application.Goto Reference:=NN
If Err < 0 Then
ActiveWorkbook.Names(j).Delete
End If
On Error GoTo 0
Next j
CS.Activate
End Sub


*** Sent via Developersdex http://www.developersdex.com ***