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.


Download Workbook

You can download the free practice Excel workbook from here.


11 Methods in Implementing VBA Macro to Insert Row in Excel

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 VBA macro.

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

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

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

Result of VBA to Insert a Single Row Based on Cell Text Value in Excel

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


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

VBA to Insert Row Based on Cell Numeric Value in Excel

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

Result of VBA to Insert Row Based on Cell Numeric Value in Excel

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


3. 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 Row 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, 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: Macro to Insert Multiple Rows in Excel (6 Methods)


4. 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:

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

VBA Macro to Insert Multiple Row of a Range in Excel

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

Result of VBA Macro to Insert Multiple Row of a Range in Excel

Read More: Excel Macro to Add Row to the Bottom of a Table


5. VBA 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 to Insert Multiple Rows Based on Predefined Condition in Excel

  • 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 to Insert Multiple Row Based on Predefined Condition in Excel

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


6. Macro to Embed 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.

VBA Macro to Insert Multiple Row 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 VBA Macro to Insert Multiple Row Based on User Input in Excel

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


Similar Readings


7. VBA Macro to Enter 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.

VBA Macro to Insert Single Row after Each Record in Excel

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.

Result of VBA Macro to Insert Single Row after Each Record in Excel

Read More: How to Insert Blank Row After Every nth Row in Excel (2 Easy Methods)


8. 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 Row 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 Row from Active Cell in Excel

Read More: How to Insert a Row within a Cell in Excel (3 Simple Ways)


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

VBA to Insert Multiple Row 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 VBA to Insert Multiple Row from Active Row in Excel

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


10. Macro to Insert 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.

VBA Macro to Insert Row without Formatting in Excel

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

Read More: Excel Formula to Insert Rows between Data (2 Simple Examples)


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

VBA Macro to Insert Copied Row in Excel

  • 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 (2 Methods)


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.


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.

We will be happy to hear your thoughts

Leave a reply

ExcelDemy
Logo