If you are working with countless macros & you want a macro to erase all the existing codes from the current excel workbook then the following article will surely help.
When you want to delete all macros from a workbook or document you can use the macro below.
The procedure can be used in both Excel and Word without any editing.
Sub RemoveAllMacros(objDocument As Object) ' deletes all VBProject components from objDocument ' removes the code from built-in components that can't be deleted ' use like this: RemoveAllMacros ActiveWorkbook ' in Excel ' or like this: RemoveAllMacros ActiveWorkbookDocument ' in Word ' requires a reference to the ' Microsoft Visual Basic for Applications Extensibility library Dim i As Long, l As Long If objDocument Is Nothing Then Exit Sub i = 0 On Error Resume Next i = objDocument.VBProject.VBComponents.Count On Error GoTo 0 If i < 1 Then ' no VBComponents or protected VBProject MsgBox "The VBProject in " & objDocument.Name & _ " is protected or has no components!", _ vbInformation, "Remove All Macros" Exit Sub End If With objDocument.VBProject For i = .VBComponents.Count To 1 Step -1 On Error Resume Next .VBComponents.Remove .VBComponents(i) ' delete the component On Error GoTo 0 Next i End With With objDocument.VBProject For i = .VBComponents.Count To 1 Step -1 l = 1 On Error Resume Next l = .VBComponents(i).CodeModule.CountOfLines .VBComponents(i).CodeModule.DeleteLines 1, l ' clear lines On Error GoTo 0 Next i End With End Sub
The applications/code on this site are distributed as is and without warranties or liability. In no event shall the owner of the copyrights, or the authors of the applications/code be liable for any loss of profit, any problems or any damage resulting from the use or evaluation of the applications/code.
"I see the tip on ""remove all macros"". But I do not know how to create the macro above. Do I copy and paste? How do I open a blank macro sheet to paste the tip into?
I have a sheet that when I open it asks me if I want to run the macro, but I do not have one created for that sheet. I'm trying to delete my ""phatom"" macro."