Macro to Insert Multiple Rows in Excel (6 Methods)

Sometimes we need to modify our large dataset in Excel by inserting rows based on certain conditions. Implementing VBA macro is the most effective, quickest and safest method to run any operation in Excel. In this article, we will show you how to insert multiple rows in Excel based on different criteria utilizing the VBA macro.

Download Workbook

You can download the free practice Excel workbook from here.

6 Methods in Implementing VBA Macro to Insert Multiple Rows in Excel

In this section, we will discuss how to insert multiple rows in Excel based on both cell value and the user predefined value by using VBA macro.

We will be considering the following dataset as our example.

1. Macro to Add Multiple Rows of a Range in Excel

To add multiple rows in a certain range of your dataset, all you have to do is pass the range inside the Range object (e.g. Range(“6:8”)). Follow this article to understand more with step by step procedures.

Steps:

• Press Alt + F11 on your keyboard or go to the tab Developer -> Visual Basic to open Visual Basic Editor.

• In the pop-up code window, from the menu bar, click Insert -> Module.

• Copy the following code and paste it into the code window.

Sub InsertMultipleRows()
'insert multiple rows as rows 6, 7 and 8
Worksheets("Rows").Range("6:8").EntireRow.Insert
End Sub

Your code is now ready to run.

• Press F5 on your keyboard or from the menu bar select Run -> Run Sub/UserForm. You can also just click on the small Play icon in the sub-menu bar to run the macro.

There will be newly inserted multiple rows as row numbers 6, 7 and 8 in the dataset.

Read More: How to Insert Multiple Rows in Excel (6 Easy Methods)

2. Macro to Embed Multiple Rows Based on User Input in Excel

In the previous section, we provided a hard-coded input value as the range of multiple rows. But in this section, we will let you know how to insert rows from users.

Steps:

• Same way as before, open Visual Basic Editor from the Developer tab and Insert a Module in the code window.
• In the code window, copy the following code and paste it.

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

Your code is now ready to run.

• Run the macro and there will be a pop-up window asking for the number of rows you want to insert.
• Write the row number and click OK.

• In the next pop-up window, you will be asked where you want your new rows to be added. Write the row address and click OK.

(In our case, we wanted 3 rows to insert in row address 6 of our dataset.)

There will be 3 newly inserted rows in the row address of 6, 7 and 8.

Read More: VBA Macro to Insert Row in Excel Based on Criteria (4 Methods)

3. VBA Macro to Insert Multiple Rows from Active Cell in Excel

Here we will learn how to add multiple rows from the active cell in our Excel dataset.

Steps:

• Same way as before, open Visual Basic Editor from the Developer tab and Insert a Module in the code window.
• In the code window, copy the following code and paste it.

Sub InsertRowsfromUser()
iMsg = InputBox("How Many Rows to Insert? (100 Rows maximum)")
numRows = Int(Val(iMsg))
If numRows > 100 Then
numRows = 100
End If
If numRows = 0 Then
GoTo EndInsertRows
End If
Do
Selection.EntireRow.Insert
iCount = iCount + 1
Loop While iCount < numRows
EndInsertRows:
End Sub

Your code is now ready to run.

• Run the macro and there will be a pop-up window asking for the number of rows you want to insert starting from the active cell.
• Write the row number (we wanted to insert 3 rows, so we wrote 3) and click OK.

You will get 3 newly inserted rows from the active cell in your dataset.

Read More: Insert Rows in Excel Based on Cell Value with VBA (2 Methods)

Similar Readings

• Excel Formula to Insert Rows between Data (2 Simple Examples)
• Excel Fix: Insert Row Option Greyed out (9 Solutions)
• Shortcuts to Insert New Row in Excel (6 Quick Methods)
• How to Insert Row Below in Excel (5 Methods)
• Excel Macro to Add Row to the Bottom of a Table

4. Macro to Include Multiple Rows from Active Row in Excel

This section will help you learn how to insert multiple rows from the active row in your Excel dataset.

Steps:

• Same way as before, open Visual Basic Editor from the Developer tab and Insert a Module in the code window.
• In the code window, copy the following code and paste it.

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

Your code is now ready to run.

• Run the macro and in the pop-up window write the row number that you want to add from the active row in your dataset (we wanted to insert 3 rows, so we wrote 3) and click OK.

There will be 3 newly inserted rows from the active row in your Excel dataset.

Read More: Excel Macro to Insert Rows (8 Methods)

5. VBA Macro to Insert Multiple Rows Based on Cell Value in Excel

Here we will insert rows based on the value presented in the cells of the dataset.

From the above dataset as our example, we will see how to add multiple rows based on the cell value.

Steps:

• Same way as before, open Visual Basic Editor from the Developer tab and Insert a Module in the code window.
• In the code window, copy the following code and paste it.

Sub InsertRowBasedonValue()
Dim iRow As Long
Dim iCount As Long
Dim i As Long
iCount = Range("A4").Value
iRow = Range("A3").Value
For i = 1 To iCount
Rows(iRow).EntireRow.Insert
Next i
End Sub

Your code is now ready to run.

• Now Run the code. When you run the macro, it takes the count of rows from Cell A4 and the row where you want to add rows starting from Cell A3.

As Cell A4 holds the value 3, so 3 rows will be included from Cell A3 in your dataset.

Read More: How to Insert Multiple Blank Rows in Excel (4 Easy Ways)

6. VBA Macro to Insert Multiple Rows Based on Predefined Condition in Excel

Look at the following example, where we already have a dataset of multiple rows and need to insert a new record as shown in the top table.

What we are going to do with this dataset is, we will insert the number of rows that are predefined by the user. That means we will insert “3 Quantity” of rows of “Name Root” and “Score 100” (from the top table) in our actual dataset (bottom table).

The steps on how to do that are given below.

Steps:

• Same way as before, open Visual Basic Editor from the Developer tab and Insert a Module in the code window.
• In the code window, copy the following code and paste it.

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

Your code is now ready to run.

• In the same way as before, Run the code and you will notice that the new record (Cell C3, D3) is inserted with the number of the Quantity predefined by the user (Cell E3).

Conclusion

This article showed you how to insert multiple rows in Excel based on different criteria by using VBA macro. I hope this article has been very beneficial to you. Feel free to ask any questions regarding the topic.

Related Articles

• How to Insert a Row within a Cell in Excel (3 Simple Ways)
• Insert Blank Row After Every nth Row in Excel (2 Easy Methods)
• How to Move a Row in Excel (6 Methods)
• Insert Rows Automatically in Excel (3 Ways)
• How to Insert a Total Row in Excel (4 Easy Methods)
• VBA to Insert Row in Excel (11 Methods)