Delete broken named ranges in sheet

[akyhne]

How to delete broken named ranges in sheet?

When you manually delete rows or columns than contains named ranges, the
named ranges in the deleted area is not deleted, but remains with a faulty
reference as (Example): MYNAMEDRANGE =SHEETS!#REFERENCE!

 

[Bob Phillips]

There is no automatic way, you need to re-instate the name.

--

HTH

RP
(remove nothere from the email address if mailing direct)

 

[Norman Jones]

Hi Akyhne.

Try:

Sub DeleteBrokenNames()
Dim Nme As Name

For Each Nme In ActiveWorkbook.Names
If Right(Nme.RefersTo, 5) = "#REF!" Then
Nme.Delete
End If
Next Nme

End Sub

 

[akyhne]

Hi Norman
Your code seams to do the job. Thanks!

"Norman Jones" skrev:

 

[Dave Peterson]

If you work with names, do yourself a favor and get a copy of 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

 

[Peter T]

Hi Dave,

Actually Name Manager two good plugs earlier in the thread. But it deserves
at least three in any topic relating to names <g>

Regards,
Peter T

 

[Dave Peterson]

But it was way at the bottom of your post!

Am I expected to scroll down, too???

<VBG>