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.

Macro to Add Multiple Rows of a Range in Excel

  • 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.

Result of Macro to Add Multiple Rows of a Range in Excel

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.

Macro to Embed Multiple Rows Based on User Input in Excel

  • 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.

Result of Macro to Embed Multiple Rows Based on User Input in Excel

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.

VBA Macro to Insert Multiple Rows from Active Cell in Excel

  • 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.

Result of VBA Macro to Insert Multiple Rows from Active Cell in Excel

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


Similar Readings


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.

Macro to Include Multiple Rows from Active Row in Excel

  • 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.

Result of Macro to Include Multiple Rows from Active Row in Excel

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.

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

  • 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.

Result of VBA Macro to Insert Multiple Rows Based on Cell Value in Excel

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.

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

  • 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).

Result of VBA Macro to Insert Multiple Rows Based on Predefined Condition in Excel


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

Sanjida Ahmed

Sanjida Ahmed

Hello World! This is Sanjida, an Engineer who is passionate about researching real-world problems and inventing solutions that haven’t been discovered yet. Here, I try to deliver the results with explanations of Excel-related problems, where most of my interpretations will be provided to you in the form of Visual Basic for Applications (VBA) programming language. Being a programmer and a constant solution seeker, made me interested in assisting the world with top-notch innovations and evaluations of data analysis.

2 Comments
  1. 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

    The above code showing the Compile Error: Wrong number of arguments or invalid property assignment.

    • I think the problem lies in the apostrophes. Use this code instead of yours. It works fine for me.

      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

      If it still doesn’t work, after pasting the code in the VBA editor, change the apostrophes of the 5th and 7th lines manually. Hope it will work then.

Leave a reply

ExcelDemy
Logo