Automating via VBA - Delete "Corrupt" Excel Names

[samadams_2006]

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.aspx?scid=kb;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

 

[Tim Zych]

'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