Empty a Module within Excel 2016 …

[Russ1701]

Using VBA in Excel 2016, how can I empty a standard module with appropriate VBA code without deleting it? Although I am able to delete a module, create a module, rename a module … I simply need to open an existing module and remove all code from within it so that it is completely empty. Thank you.

 

[Norie]

You can use the DeleteLines method to delete the code from a module.

For example, this will delete all the code from Module1.

Sub DeleteAllCodeFromModule()
Dim IDE As Object
Dim mdl As Object

    Set IDE = ActiveWorkbook.VBProject

    Set mdl = IDE.VBComponents("Module1").CodeModule

    mdl.DeleteLines StartLine:=1, Count:=mdl.CountOfLines

End Sub

PS Why do you want to do this?

 

[Rick Rothstein]

… I simply need to open an existing module and remove all code from within it so that it is completely empty.

Why not simply click anywhere in the module, press CTRL+A followed by the Delete key? Seems easy and fast enough to do to me.

 

[Russ1701]

Yes, CTRL+A & Delete does indeed work, but I am automating the process programmatically, without human intervention. I tried SendKeys but does not always work correctly, at its best (not to mention that it is best avoided during coding). Thanks.