How to Use VBA to Insert Row in Excel (11 Methods)

Sometimes we need to modify our large dataset in Excel by inserting rows according to specific requirements. 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 the row in Excel with VBA macro.

In this section, we will discuss how to insert a single row and multiple rows in Excel based on cell values, user-predefined values, etc. by using the VBA macro.

1. Applying VBA to Insert a Single Row Based on Cell Text Value in Excel

Consider the following dataset where we will be inserting a new row based on a specific text value of a cell. And we set the value as “Insert Above”. So, whenever we run the macro, there will be a newly inserted row right above the row containing that specific cell value.

The steps to insert a new row based on the cell-specific text value with the VBA macro are given below.

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 InsertRowsBasedonCellTextValue()
'Declare Variables
Dim LastRow As Long, FirstRow As Long
Dim Row As Long
With ActiveSheet
    'Define First and Last Rows
    FirstRow = 1
    LastRow = .UsedRange.Rows(.UsedRange.Rows.Count).Row
    'Loop Through Rows (Bottom to Top)
    For Row = LastRow To FirstRow Step -1
        If .Range("B" & Row).Value = "Insert Above" Then
            .Range("B" & Row).EntireRow.Insert
        End If
    Next Row
End With
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.

You will notice that there will be a newly inserted row right above the cell containing the “Insert Above” text value.

Read More: Excel VBA: Insert Row with Values

2. Using Macro to Embed Row Based on Cell Numeric Value in Excel

We have learned how to insert a row based on a specific cell text value. But in this section, we will learn how to insert a row based on a specific numeric value in Excel.

Consider the following dataset where we will be inserting a new row right above the cell containing 99.

The steps to insert a new row based on the cell-specific numeric value with the VBA macro 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 InsertBlankRowsBasedOnCellNumericValue()
    Dim Col As Variant
    Dim BlankRows As Long
    Dim LastRow As Long
    Dim R As Long
    Dim StartRow As Long
        Col = "C"
        StartRow = 1
        BlankRows = 1
            LastRow = Cells(Rows.Count, Col).End(xlUp).Row
            Application.ScreenUpdating = False
            With ActiveSheet
For R = LastRow To StartRow + 1 Step -1
If .Cells(R, Col) = "99" Then
.Cells(R, Col).EntireRow.Insert Shift:=xlDown
End If
Next R
End With
Application.ScreenUpdating = True
End Sub

Your code is now ready to run.

• In the same way as before, Run the code and you will notice that there will be a newly inserted row right above the cell containing 99.

3. Inserting Multiple Rows Based on Cell Value Using VBA Macro 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, 3 rows will be included from cell A3 in your dataset.

Read More: Insert Rows in Excel Based on Cell Value with VBA

4. Utilizing 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 about step-by-step procedures.

Steps:

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

• Run the code and there will be newly inserted multiple rows as row numbers 6, 7, and 8 in the dataset.

5. Inserting Multiple Rows Based on Predefined Conditions 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.

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

Read More: VBA Macro to Insert Row in Excel Based on Criteria

6. Embedding Multiple Rows Based on User Input in Excel

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

Steps:

• As shown previously, 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.

7. Using VBA Macro to Enter a Single Row after Each Record in Excel

What if you want to have some spaces by inserting rows after each record in your large dataset?

With the following dataset as an example, we will show you how to do that using VBA in Excel.

The steps to insert a single row after each record in Excel 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 InsertRow()
Dim i As Long
For i = Cells(Cells.Rows.Count, "B").End(xlUp).Row To 3 Step -1
If Cells(i, "B") <> Cells(i - 1, "B") Then Rows(i).EntireRow.Insert
Next i
End Sub

Your code is now ready to run.

You can change Cells(lRow – 1, “B”) to whatever value you want to trigger your row insert and, of course, what column this is being applied to.

• Run the code and you will notice that there are new inserted single rows after each old record.

8. Inserting 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.

9. Using VBA 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 Add Row to Bottom of a Table

10. Inserting Row without Formatting in Excel

If you want to insert rows without following any format from the rows above or below then do as shown in the steps below.

Steps:

• 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 InsertRowWithoutFormatting()
Dim iRow As Long
iRow = 6
With Worksheets("Format")
.Rows(iRow).Insert Shift:=xlShiftDown
.Rows(iRow).ClearFormats
End With
End Sub

Your code is now ready to run.

• Run this piece of code and there will be a newly inserted row without formatting.

11. Utilizing VBA Macro to Insert Copied Row in Excel

Steps to insert a previously copied row in another specific row number 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 InsertCopiedRow()
With Worksheets("Copy")
.Rows(6).Copy
.Rows(10).Insert Shift:=xlShiftDown
End With
Application.CutCopyMode = False
End Sub

Your code is now ready to run.

• Run this code and it will copy row number 6 and paste it in row number 10.

Read More: Macro to Insert Row and Copy Formula in Excel

Download Practice Workbook

You can download the free practice Excel workbook from here.

Conclusion

This article showed you how to insert a single row and 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.