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.
Answered By
alphae
15 points
N/A
#83268
Excel module to insert lines
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
alphae
15 points
N/A
#83269
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.
