macro to delete all macros?

[Myke]

I have a directory with about 300+ files with macro's attached to them. Any macros on the sheets are completely redundant and we have to remove them. However, opeining them individually and removing any macros is a VERY tedious process. Can I do this automatically in any way (i.e. create a button on the taskbar which will delete any and all macro's on the active sheet.

This would save me Sooooooo much time.

 

[Sean Stevens]

Chip Pearson's site has some stuff to do this.

http://www.cpearson.com/excel/vbe.htm

 

[anvil19]

Hi Myke

This code will delete modules from your workbook.

Dim m As Object
        Dim mCtr As Integer
        Dim oCtr As Variant
        Dim vbP As Object
        Dim strNames As String
        strNames = ThisWorkbook.Name
        mCtr = 0
    
        Set vbP = Workbooks("" & strNames).VBProject.VBComponents
    
        For oCtr = 1 To vbP.Count
            mCtr = mCtr + 1
            If vbP(mCtr).Type = 1 Then
               Set m = vbP
                   m.Remove vbcomponent:=m.Item(m(mCtr).Name)
               mCtr = mCtr - 1
            End If
        Next

You will need to explicitly save each workbook after the modules are removed. Saving in code has no effect (??? - Odd I know).

If you have code in sheets or forms etc then change the

If vbP(mCtr).Type = 1 Then

line to recognise other types of objects. The Visual Basic Help has all the details (from the VBProject Collection).

Try it!

anvil19