How to delete rows in a code (not by hand !) ?

[ExcelTeen]

Hello everybody,

who can tell me what is wrong with my code ? (how do I attach a file ?)

From a sub() I want to delete some rows in another Sub()
In this case from row 3 to the next to last row.

As the length of my Sub() is never the same, I HAVE to put the number of rows in a variable.
But it aborts when instruction deletelines is used !

What should I do ?

In advance, thank you for your help

Sub Squeeze()
Dim Start As Integer, TheEnd As Integer, TargettedFirstRow As Integer, TargettedLastRow As Integer
With ThisWorkbook.VBProject.VBComponents("Module2").CodeModule
        Start = .ProcStartLine("Program", 0)
       ' gives position of the first row in Sub Program()
        TheEnd = .ProcCountLines("Program", 0)
       '  gives position of the last row in Sub Program()
        TargettedFirstRow = Start + 2
       ' because I want to keep at least one row between Sub() et End Sub()
        TargettedLastRow = TheEnd - 1 
       ' because I want to keep the row where End Su() is written
       
        MsgBox "it begins at " & Start & " and finishes at " & TheEnd
        MsgBox "First row to squeeze is " & TargettedFirstRow & " and last row to squeeze is " & TargettedLastRow
       
        .deleteLines TargettedFirstRow, TargettedLastRow
        ' here it creates problem !!!
 
End With
End Sub

 

[oldbrewer]

a sub() is a macro - you cannot delete rows of coding in a macro with another macro

do you mean you want to use macro code to delete certain rows on a sheet ?

 

[ExcelTeen]

Hi Oldbrewer,

Thank you for your answer, sorry for mine so late but the notification landed in my spambox !

I have already been successfull in removing code rows in a Sub() from another Sub().

For instance, the following code does the job

Sub Squeeze_working()
Dim Start As Integer, TheEnd As Integer
With ThisWorkbook.VBProject.VBComponents("Module2").CodeModule
        Start = .ProcStartLine("Program", 0) ' gives position of the first row in Sub Program()
        TheEnd = .ProcCountLines("Program", 0) '  gives position of the last row in Sub Program()
      
        .deleteLines Start, TheEnd - Start - 1
        ' this works !
 
End With
End Sub

But when I put as above (first message) values for .deleteLines x,y, then it aborts !

What is the reason ?

In advance, thank you

 

[oldbrewer]

why are you using a macro to delete lines in another macro ?

normally the logic within the macro does what you want with your data..........

 

[ExcelTeen]

you are right but I want to execute instructions and then clean everything. I cannot remove the procedure because within a project, the procedure has to remain.

 

[oldbrewer]

since this approach is completely new to me, I regret I cannot assist. suggest you close the thread and open a new one. although try defining x and y as variables before assigning them values prior to running the macro. good luck