'Deletes bad range names
Sub DeleteBadRangeNames()
Dim nm As Name
For Each nm In ThisWorkbook.Names
If nm.RefersTo Like "*[#]REF*" Then
' View in the immediate window first
debug.print nm.RefersTo
' If satisfied with bad-name list, uncomment
' and run again:
' nm.Delete
End If
Next 'nm
End Sub
I commented out the actual delete action so you can check the bad names
first. If you have a lot, you might want to print them out to a new
workbook, because they won't fit in the immediate pane. Both macros go in
the workbook with the bad range names.
Sub DeleteBadRangeNames2()
Dim nm As Name
Dim wkbWithNames As Workbook, wkbOutput As Workbook
Set wkbWithNames = ThisWorkbook
Set wkbOutput = Workbooks.Add(1)
For Each nm In wkbWithNames.Names
If nm.RefersTo Like "*[#]REF*" Then
' Print out to a new workbook first
With wkbOutput.Worksheets(1).Cells(65000, 1).End(xlUp).Offset(1)
.Value = nm.Name
.Offset(, 1).Value = "'" & nm.RefersTo
End With
' If satisfied with bad-name list, uncomment
' and run again:
' nm.Delete
End If
Next
End Sub
<(E-Mail Removed)> wrote in message
news:(E-Mail Removed)...
>
> Hello,
>
> I have some corrupt excel names in spreadsheets I'm sent from time to
> time. I can get rid of these via the nformation outlined in the
> Microsoft article:
>
> http://support.microsoft.com/default...b;en-us;555127
>
> but I would like to "Automate" this. I know that there is a VBA Guru
> out there that can help me out. Perhaps you need to use the
> FindWindow API? Perhaps there is another way.
>
> Can someone send me some sample code that will automate this deletion
> process rather than having to delete every single corrupt name
> individually?
>
> Thanks
> Sam
>
Similar Threads
