Prev: More on saving as binary.
Next: MSN MoneyCentral stock quote
From: Kevryl on 1 Jun 2010 06:56
Excel 2007

I have pasted a list of all range names (368 of them in all) and I find a
few that have been obsoleted through their location being cut out (eg
"=#REF!$H$1
"). These show ikn the pasted list but not in the range names editing
dialogue box.

Is there a way to delete them? "Edit" and "Delete" in Formulas / Name
Manager are greyed out.
From: Gary''s Student on 1 Jun 2010 07:18
This little macro loops over your defined names. If it finds #REF in
RefersTo, the name is deleted:

Sub dural()
Dim s1 As String, s2 As String, s3 As String
Dim s4 As String
s3 = "#REF"
For Each n In ActiveWorkbook.Names
s1 = n.Name
s2 = n.RefersTo
s4 = Replace(s2, s3, "")
If Len(s2) <> Len(s4) Then
ActiveWorkbook.Names(s1).Delete
End If
Next
End Sub
--
Gary''s Student - gsnu201003


"Kevryl" wrote:

> Excel 2007
>
> I have pasted a list of all range names (368 of them in all) and I find a
> few that have been obsoleted through their location being cut out (eg
> "=#REF!$H$1
> "). These show ikn the pasted list but not in the range names editing
> dialogue box.
>
> Is there a way to delete them? "Edit" and "Delete" in Formulas / Name
> Manager are greyed out.
From: Don Guillett on 1 Jun 2010 08:40
Option Explicit
Sub deletenameswithREF()
Dim n As Name 'String
For Each n In ThisWorkbook.Names
'MsgBox n.RefersTo 'Name
If InStr(n.RefersTo, "#REF") > 0 Then n.Delete
Next
End Sub

--
Don Guillett
Microsoft MVP Excel
SalesAid Software
dguillett(a)gmail.com
"Kevryl" <Kevryl(a)discussions.microsoft.com> wrote in message
news:8C340A4F-5BB0-4AEF-BA09-3F0CD0BF9467(a)microsoft.com...
> Excel 2007
>
> I have pasted a list of all range names (368 of them in all) and I find a
> few that have been obsoleted through their location being cut out (eg
> "=#REF!$H$1
> "). These show ikn the pasted list but not in the range names editing
> dialogue box.
>
> Is there a way to delete them? "Edit" and "Delete" in Formulas / Name
> Manager are greyed out.

From: Kevryl on 2 Jun 2010 01:27
Thank you to Gary"s Student and Don.

looks like this forum may be getting dismatled. Can't get into your replies
to respond individually.
Cheers

"Kevryl" wrote:

> Excel 2007
>
> I have pasted a list of all range names (368 of them in all) and I find a
> few that have been obsoleted through their location being cut out (eg
> "=#REF!$H$1
> "). These show ikn the pasted list but not in the range names editing
> dialogue box.
>
> Is there a way to delete them? "Edit" and "Delete" in Formulas / Name
> Manager are greyed out.
 | 
Pages: 1
Prev: More on saving as binary.
Next: MSN MoneyCentral stock quote