How to Insert Multiple Rows In Excel using VBA

Excel comes with much excellent inbuilt functionality that leads to automation of activities. This is actually one of the advantages of spreadsheets. Inserting many multiple rows at once in excel can now be done in a single click without actually inserting the rows one by one with the help of VBA codes and formulas. Inserting multiple rows in excel is thus one of the fantastic things that one can do. To insert rows in Excel, kindly follow the following procedure:

Steps to Insert Multiple Rows in Excel using VBA

1. Ensure that your excel work has a developer tab as follows. If not, activate it as follows:

2. After Looking for the developer tab and finding out that it does not exist by default please do the following. Press the "File" tab.

This will open a window that has a pane containing a list of commands on its left. Some of the commands include Save, save as, open, close, info, recent, new, print, save &send, Help, Options, and Exit.

From this list of commands that are actually descending from top to bottom select "Options" located towards the bottom left.

3. Another window with Excel options will appear with a similar layout to the previous one.

Click the "customize ribbon" option to open yet another window and select the developer checkbox as shown below. Then click OK. The Developer tab should now be activated.

4. Now click the developer tab on the ribbon.

5. Now navigate to the group named "code" and select the "Visual Basic" icon.

This icon is usually located near the macros tab.

A blank window named Microsoft Visual Basic for Applications" will appear.

6. Create a new module or optionally click sheet1 and Type the following code as it appears in the code editor as below:

The code is:

Sub insertMultipleRows()

'code disables excel while macro runs'

Application.Calculation = xlCalculationManual

Application.EnableEvents = False

Application.ScreenUpdating = False

Dim rowcount As Integer

rowcount = Application.InputBox(Prompt:="how many rows do you want to insert" & ActiveCell.Row & "?", Type:=1)

'handle error for instances when row count may be negative or zero'

If rowcount <= 0 Then End

Rows(ActiveCell.Row & ":" & ActiveCell.Row + rowcount – 1).Insert Shift:=xlDown

Application.Calculation = xlCalculationAutomatic

Application.EnableEvents = True

Application.ScreenUpdating = True

End Sub

So the code will appear as follows in the VBA editor

7. Save your codes using the SAVE button. Note that you have to as a macro enable workbook for the code to work.

8. Run the macro by clicking the RUN tab while still in developer mode as follows.

You can optionally click the green right-pointing arrow located under Debug menu tab on the VBA ribbon.

9.  After clicking run a dialog box with the title "Input" will show up.

10. Enter the number of rows you want to insert and select "OK" after keying the number of rows to insert

Using VBA To Insert Multiple Rows Based on Predefined Condition In Excel

In this method, you always place the number of rows that you have predefined yourself. For example, place”3 Quantity” of rows of “Name Root” and “Score 100” starting from the upper part to the lower part of your Excel.

1.  Go to the Developer tab and open Visual Basic Editor. Then, place a Module in the code window.

2.  Copy and paste the code window below;

 Sub InsertRows()

Qty = Range("E3").Value

Range("B12").Select

ActiveCell.Rows("1:" & Qty).EntireRow.Select

Selection.Insert Shift:=xlDown

ActiveCell.Select

Range("B12").Resize(Qty, 2).Value = Range("C3:D3").Value

End Sub

3.  Your code will be available to use.

4.  As you are making the code in use, you will notice a new record (Cell C3, D3) as well as the number of the amount predefined by the user (Cell E3).

This method specializes in placing rows from the users.

Steps:

1.  Go to the Developer tab and open Visual Basic Editor. After that, place a Module in the code window.

2.  Copy and Paste the code.

Sub InsertRowsfromUserInput()

Dim iRow As Long

Dim iCount As Long

Dim i As Long           

iCount = InputBox(Prompt:="How Many Rows to Insert?")

iRow = InputBox _

(Prompt:="Where to Insert New Rows? (Enter the Row Number)")

For i = 1 To iCount

Rows(iRow).EntireRow.Insert

Next i

End Sub

3.  As you try to use the code, go to the pop-up window which will question, how many the number of rows will you place.

4.  Type the row number and tap the OK option.

5.  Go back to the pop-up window where you will be questioned again about which row address you wish to place your rows. Type the row address and tap the OK option. For example, you may say you want 3 rows to be placed in row address 6 of your work.

6.  You will see new rows being placed at the addresses you have mentioned. In row addresses 6, 7, and 8 there will be 3 newly placed rows.

VBA To Include Multiple Rows from Active Row in Excel

Steps;

1.  Go to the Developer tab and open Visual Basic Editor. Then, place a Module in the code window.

2.  Copy and paste the following code into the code window.

Sub InsertRowsfromActiveRow()

Dim iRows As Integer

Dim iCount As Integer

'Select the current row

ActiveCell.EntireRow.Select

On Error GoTo Last

iRows = InputBox("Enter Number of Rows to Insert", "Insert Rows")

'Keep on inserting rows until we reach the input number

For iCount = 1 To iRows

Selection.Insert Shift:=xlToDown, CopyOrigin:=xlFormatFromRightorAbove

Next iCount   

Last: Exit Sub

End Sub

3.  As you use the macro, in the pop-up window type the row number that you need to place from the range of data you have. From your example in the previous method, you will type 3 rows and tap on the OK option.

4.  Finally, you note that 3 new rows are placed in the initial row of your excel similar to the previous method.