Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.programming
|
|||
|
|||
Automating via VBA - Delete "Corrupt" Excel Names
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 |
#2
Posted to microsoft.public.excel.programming
|
|||
|
|||
Automating via VBA - Delete "Corrupt" Excel Names
'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 wrote in message oups.com... 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 |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
Delete emty file names from "recent documents" Excel | Excel Discussion (Misc queries) | |||
Excel "Move or Copy" and "Delete" sheet functions | Excel Worksheet Functions | |||
Delete "Unused names" in a batch | Excel Discussion (Misc queries) | |||
Delete All Names - Even Invalid "#REF" Names | Excel Programming | |||
Understanding: "BUG: VB Hangs While Automating Excel Using OLE Control" | Excel Programming |