Excel module to insert lines

Excel module to insert lines

You can try this method too, hope it helps.

If you are using ms excel 2010, open the file you are working on and save a copy of it somewhere else for safety before going on with the procedure I am about to give you. Done that?

Open you file in excel

Press ALT + F11 on your keyboard to open the Visual Basic Editor

Right click on the name of document and choose insert followed by module

A blank window will appear. Copy and paste the following code in it:

Sub Insert_Blank_Rows ()

‘Select last row in worksheet.

Selection.End(xlDown).Select

Do Until ActiveCell.Row=1

‘Insert blank row.

ActiveCell.EntireRow.Insert shift:=xlDown

‘Move up one row.

ActiveCell.Offset(-1,0).Select

Loop

End Sub

Press control S on the keyboard to save the code

Go back to excel

In view, click macros, view macros, select run.

Excel module to insert lines

It is very possible to add a code into a module without the need to use a different text file that contains that code.below is a macro that illustrates this. You can go ahead and customize it so that you will be able to put the code you desire.

Sub InsertProcedureCode(ByVal wb As Workbook, ByVal InsertToModuleName As String)

' inserts new code in module named InsertModuleName in wb

' needs customizing depending on the code to insert

Dim VBCM As CodeModule

Dim InsertLineIndex As Long

    On Error Resume Next

    Set VBCM = wb.VBProject.VBComponents(InsertToModuleName).CodeModule

    If Not VBCM Is Nothing Then

        With VBCM

            InsertLineIndex = .CountOfLines + 1

            ' customize the next lines depending on the code you want to insert

            .InsertLines InsertLineIndex, "Sub NewSubName()" & Chr(13)

            InsertLineIndex = InsertLineIndex + 1

            .InsertLines InsertLineIndex, _

                "    Msgbox ""Hello World!"",vbInformation,""Message Box Title""" & Chr(13)

            InsertLineIndex = InsertLineIndex + 1

            .InsertLines InsertLineIndex, "End Sub" & Chr(13)

            ' no need for more customizing

        End With

        Set VBCM = Nothing

    End If

    On Error GoTo 0

End Sub

Example:

InsertProcedureCode Workbooks("WorkBookName.xls"), "Module1"

That is all you need.