Excel module to insert lines

Asked By 0 points N/A Posted on -
qa-featured

 

I need a module or macro which help me to solve the flowing problem in excel. I have a sheet I have 292 cases and I am looking to triple each case by inserting two more lines after each line, hope that you can help.  

SHARE
Best Answer by alphae
Best Answer
Best Answer
Answered By 10 points N/A #83268

Excel module to insert lines

qa-featured

This looks quite easy but it may help you a great deal.

  • Open the excel document you are working on
  • Go to the first row that has data
  • Press shift + spacebar on the keyboard to select everything in the current row
  • With the line is still selected, press control+shift+the_plus_sign key combination on the keyboard three times.
  • Move down the other rows and do the same for all the  rows that you want to triple by three.

It is quite simple to use in this case considering that you have a lot of data already entered as opposed to using insert rows option in the menu. It will save you some time.

Answered By 10 points N/A #83269

Excel module to insert lines

qa-featured

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.

Answered By 0 points N/A #83270

Excel module to insert lines

qa-featured

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.

Login/Register to Answer

Related Questions