VBA to delete Sub or Private Sub in ThisWorkbook from a Module

Hello @MagnusNovak. A similar issue was resolved here. If you only need to remove the code from the ThisWorkbook module, then here is the option for you.

Option Explicit

Sub DeleteCodeFromThisWorkbook()
    Dim VBProj      As VBIDE.VBProject: Set VBProj = ThisWorkbook.VBProject
    Dim VBComp      As VBIDE.VBComponent

    For Each VBComp In VBProj.VBComponents

        If VBComp.Name = "ThisWorkbook" Then
            Dim CodeMod As VBIDE.CodeModule: Set CodeMod = VBComp.CodeModule
            CodeMod.DeleteLines 1, CodeMod.CountOfLines
            Exit For
        End If

    Next VBComp

End Sub

Good luck.

Hello @MagnusNovak. Yes,

this code deletes all of the code from ThisWorkbook

According to Chip Pearson's site. We can find the procedure we need. The code was taken from there and modified to suit your needs.

Sub SearchCodeModule()
    Dim VBProj      As VBIDE.VBProject
    Dim VBComp      As VBIDE.VBComponent
    Dim CodeMod     As VBIDE.CodeModule
    Dim FindWhat    As String
    Dim SL          As Long    ' start line
    Dim EL          As Long    ' end line
    Dim SC          As Long    ' start column
    Dim EC          As Long    ' end column
    Dim Found       As Boolean
    Dim ProcStart   As Long
    Dim ProcEnd     As Long

    Set VBProj = ActiveWorkbook.VBProject
    Set VBComp = VBProj.VBComponents("ThisWorkbook")   ' Here we indicate the module we need
    Set CodeMod = VBComp.CodeModule

    FindWhat = "Workbook_SheetSelectionChange"   ' Here we indicate the procedure we need

    With CodeMod
        SL = 1
        EL = .CountOfLines
        SC = 1
        EC = 255
        Found = .Find(Target:=FindWhat, StartLine:=SL, StartColumn:=SC, _
                EndLine:=EL, EndColumn:=EC, _
                wholeword:=True, MatchCase:=False, patternsearch:=False)
        Do Until Found = False
            Debug.Print "Found at: Line: " & CStr(SL) & " Column: " & CStr(SC)
            ProcStart = .ProcStartLine("Workbook_SheetSelectionChange", vbext_pk_Proc)
            
            If ProcStart <> 0 Then
                ProcEnd = .ProcCountLines("Workbook_SheetSelectionChange", vbext_pk_Proc)
                .DeleteLines ProcStart, ProcEnd
                Exit Do
            End If
            
            EL = .CountOfLines
            SC = EC + 1
            EC = 255
            Found = .Find(Target:=FindWhat, StartLine:=SL, StartColumn:=SC, _
                    EndLine:=EL, EndColumn:=EC, _
                    wholeword:=True, MatchCase:=False, patternsearch:=False)
        Loop

    End With

End Sub

Objects In The VBA Extensibility Model

The following is a list of the more common objects that are used in the VBA Extensibilty object model. This is not a comprehensive list, but will be sufficient for the tasks at hand.

VBIDE
The VBIDE is the object library that defines all the objects and values that make up VBProject and the Visual Basic Editor. You must reference this library to use the VBA Extensibility objects. To add this reference, open the VBA editor, open your VBProject in the editor, and go to the Tools menu. There, choose References . In the References dialog, scroll down to Microsoft Visual Basic for Applications Extensibility 5.3 and check that item in the list.

Good luck.