30 Advanced Excel Tips for 2026

Excel Champs / VBA / How to Insert a Row using VBA in Excel
By Puneet Gogia (Microsoft MVP)

How to Insert a Row using VBA in Excel

30+ Advanced Excel Tips for 2026

No spam. Only Excel Stuff. God Promise. 😊

Rows(5).Insert inserts a blank row above row 5 and moves row 5 and everything below it down. For several rows at once, give a range of rows: Rows("5:7").Insert inserts three. Name the sheet, as in Worksheets("Data").Rows(5).Insert, when another sheet might be active.

The new row takes its formatting from the row above. To take it from the row below, add CopyOrigin:=xlFormatFromRightOrBelow, or clear it afterwards with Rows(5).ClearFormats.

To insert a copy of an existing row, copy it first: Rows(2).Copy followed by Rows(10).Insert inserts the copied row above row 10, with its values and formatting. Then run Application.CutCopyMode = False to clear the copy marquee.

When inserting rows in a loop, go from the bottom up, as in For r = lastRow To 2 Step -1. Each insert pushes the rows below it down, so a loop that goes down the sheet meets the same row again or skips rows. This inserts a blank row wherever the value in column A changes:

Dim r As Long
For r = Cells(Rows.Count, "A").End(xlUp).Row To 3 Step -1
    If Cells(r, "A").Value <> Cells(r - 1, "A").Value Then Rows(r).Insert
Next r

Imagine you’re working on a sales report in Excel, and you need to add rows between each salesperson’s records to make the report easier to read. You have a list of sales data where each salesperson has multiple rows of data, and you want to insert a blank row after every three rows to separate each salesperson’s section.

This tutorial will discuss different ways to write code in VBA to create a macro that can help you insert rows in a worksheet.

Insert a Single Row using VBA

To insert a row using a VBA code, use the “EntireRow” property with the “Insert” method. With the EntireRow property, you can refer to the entire row using a cell and then insert a new row there. By default, it will insert a single row before the cell that you have mentioned.

insert a single row using vba
  1. First, specify a cell using the range object.
  2. Now, enter a dot (.) to get the list of properties and methods.
  3. After that, select the “EntireRow” property or type it.
  4. Again, enter a dot (.) and select the “Insert” method or type it.
Range("A1").EntireRow.Insert

Your code is ready here to insert a row. When you run this code, it will instantly insert a new row above row 1 (the row of cell A1). If you want to insert a column, you can use the code below:

Range("A1").EntireColumn.Insert

Insert Multiple Rows

I have found two ways to insert multiple rows in a worksheet. The first is the same insert method we used in the above example.

With this, you need to specify a range whose row count is equal to the number of rows you want to insert. Now, let’s say you want to insert 5 rows; in that case, you can use a code like the following.

insert a multiple rows

For example, the following line inserts five new rows above row 3 (rows 3 to 7 become empty, and the old row 3 moves to row 8):

Rows("3:7").Insert

To be honest, I haven’t found this method quite useful because you need to change the range to change the row count. So here’s the second method.

Dim iRow As Long
Dim iCount As Long
Dim i As Long

iCount = InputBox(Prompt:="How many rows you want to add?")
iRow = InputBox _
(Prompt:="After which row you want to add new rows? (Enter the row number)")

For i = 1 To iCount
Rows(iRow + 1).EntireRow.Insert
Next i

When you run this code, it asks you to enter the number of rows you want to add and the row number where you want to add all those rows. It uses a FOR LOOP (For Next) to loop that number of times and insert rows one by one. As the prompt asks for the row after which to add the rows, the code inserts them at iRow + 1. Like the other short codes on this page, put these lines between Sub and End Sub in a module to run them.

Note: If a worksheet is protected, you won’t be able to insert rows using VBA code. But you can still insert rows if the worksheet protection allows it. This can be done by enabling the “Insert Rows” option when you protect the worksheet.

Insert Rows Based on the Cell Values

If you want to insert rows based on a cell value, use the following code.

Dim iRow As Long
Dim iCount As Long
Dim i As Long

iCount = Range("A1").Value
iRow = Range("B1").Value

For i = 1 To iCount
Rows(iRow).EntireRow.Insert
Next i

When you run this macro, it takes the count of rows from cell A1 and the row number from cell B1, and it adds the new rows above that row.

Insert a Row without Formatting

When you insert a row where the above row has some specific formatting, the row will also have that formatting automatically. And the simplest way to deal with this is to use ClearFormats. Consider the following code.

Rows(7).EntireRow.Insert
Rows(7).ClearFormats

When you run the above code, it inserts a new row before the 7th row. That new row becomes the 7th row, and the second line of code clears the formats from that row.

Insert Copied Row

You can also use the same method to copy a row and then insert it somewhere else. See the following code.

Application.CutCopyMode = False
With Worksheets("Data")
.Rows(5).Copy
.Rows(9).Insert Shift:=xlShiftDown
End With
Application.CutCopyMode = False

This code copies row 5 of the “Data” sheet and inserts the copied row above row 9. In the end, CutCopyMode = False clears the copy mode, so the moving border around row 5 goes away.

Insert Rows Based on the Selection

It inserts rows into a worksheet based on the number of rows you have selected. First, it sets the active worksheet and identifies the range of cells you’ve selected.

Then, it counts the number of rows in your selection and inserts that number of rows starting at the first row of your selection, pushing existing rows down to make space.

Sub insert_rows_based_on_selection()
Dim ws As Worksheet
Dim selectedRange As Range
Dim numRows As Integer

Set ws = ActiveSheet
Set selectedRange = Selection

numRows = selectedRange.Rows.Count

' Insert rows based on the number of rows selected
ws.Rows(selectedRange.Row & ":" & selectedRange.Row + numRows - 1).Insert Shift:=xlDown

MsgBox numRows & " rows inserted at position " & selectedRange.Row & "."
End Sub

After inserting the rows, a message tells you how many rows were inserted and at which position. This allows you to quickly add multiple rows at a specific location in your worksheet by simply selecting the desired range and running the code.

Insert Alternate Rows from a Specific Cell

This code helps you insert alternate rows starting from a cell you select. First, it asks you to choose the starting cell; then, it asks how many alternate rows you want to insert.

For example, if you select cell A1 and want to insert three rows, you get a blank row above each of the first three rows of data, so the data in rows 1 to 3 ends up in rows 2, 4 and 6.

Sub insert_alternate_rows()
Dim ws As Worksheet
Dim rng As Range
Dim startCell As Range
Dim numRows As Integer
Dim i As Integer
Dim firstRow As Long

' Set the worksheet to the active sheet
Set ws = ActiveSheet

' Prompt the user to select the starting cell
On Error Resume Next
Set startCell = Application.InputBox("Select the starting cell", Type:=8)
On Error GoTo 0

' Check if the user selected a cell
If startCell Is Nothing Then
MsgBox "No cell selected. Exiting..."
Exit Sub
End If

' Prompt the user to input the number of alternate rows to insert
numRows = Application.InputBox("Enter the number of alternate rows to insert:", Type:=1)

' Check if the user entered a valid number
If numRows <= 0 Then
MsgBox "Invalid number. Exiting..."
Exit Sub
End If

' Insert the alternate rows
firstRow = startCell.Row
For i = 1 To numRows
ws.Rows(firstRow + (i - 1) * 2).Insert Shift:=xlDown
Next i

MsgBox "Done! " & numRows & " alternate rows inserted."
End Sub

The code works by looping through the number of rows you entered and inserting a row at every second position from the row of the starting cell. It stores that row number in firstRow first, because the startCell range moves down each time a row is inserted above it.

After the loop, a message shows how many rows were inserted. This allows you to quickly add rows to your worksheet without manually doing it.

Insert Rows at Every Nth Row

It inserts rows at every nth interval in a worksheet. It starts by setting the active worksheet and defining the interval (n) as every 3rd row. It also specifies the starting row (startRow) as 2 and the ending row (endRow) as 20.

The code then loops from the end to the start row, inserting a row at every nth position and working backward to ensure the correct rows are shifted down.

Sub insert_rows_every_nth_row()
Dim ws As Worksheet
Dim i As Long
Dim n As Long
Dim startRow As Long
Dim endRow As Long

Set ws = ThisWorkbook.Sheets("Sheet1") ' Change to your specific sheet name
n = 3 ' Specify the interval (every nth row)
startRow = 2 ' Specify the starting row
endRow = 20 ' Specify the ending row

For i = endRow To startRow Step -n
ws.Rows(i).Insert Shift:=xlDown
Next i

MsgBox "Rows inserted at every " & n & "th row between rows " & startRow & " and " & endRow & "."
End Sub

After inserting the rows, a message appears stating that rows were inserted at every third row between the specified start and end rows.

Insert Row with Formatting

When you run this code, it inserts the selected row multiple times into a specified cell location in the worksheet. First, it checks if you have chosen a single row. If not, it asks you to select one.

Then, it asks you to choose a cell where the new rows will be inserted and how many times you want to insert the selected row.

After you specify the cell and the number of times, it copies the selected row and inserts it the specified number of times above the row of that cell. As the row is copied before the Insert, Excel inserts the copied row with its values and formatting.

Finally, it shows a message box confirming the number of rows inserted.

Sub insert_row_with_formatting()
Dim ws As Worksheet
Dim selectedRow As Range
Dim insertCell As Range
Dim numRows As Integer
Dim i As Integer

' Set the worksheet to the active sheet
Set ws = ActiveSheet

' Check if a single row is selected
If Selection.Rows.Count <> 1 Then
MsgBox "Please select a single row to copy and insert."
Exit Sub
End If

Set selectedRow = Selection.Rows(1)

' Prompt the user to select the cell where rows will be inserted
On Error Resume Next
Set insertCell = Application.InputBox("Select the cell where rows will be inserted:", Type:=8)
On Error GoTo 0

' Check if the user selected a cell
If insertCell Is Nothing Then
MsgBox "No cell selected. Exiting..."
Exit Sub
End If

' Prompt the user to input the number of times to insert the row
numRows = Application.InputBox("Enter the number of times to insert the row:", Type:=1)

' Check if the user entered a valid number
If numRows <= 0 Then
MsgBox "Invalid number. Exiting..."
Exit Sub
End If

' Insert the row with formatting the specified number of times
For i = 1 To numRows
selectedRow.EntireRow.Copy
insertCell.EntireRow.Insert Shift:=xlDown
' Insert pastes the copied row with its values and formatting
Next i

' Clear the clipboard
Application.CutCopyMode = False

MsgBox numRows & " rows inserted at the specified cell."
End Sub

Insert Rows on Multiple Sheets

This code helps you insert rows across all worksheets in the workbook. First, it asks you to enter the row number where you want to insert new rows and checks if the number is valid. Then, it asks how many rows you want to insert and ensures this number is valid.

After that, it loops through each worksheet in the workbook and inserts the specified number of rows at the specified row number on each sheet. Finally, it shows a message confirming how many rows were inserted and at which position.

Sub insert_rows_on_multiple_sheets()
Dim ws As Worksheet
Dim rowNum As Long
Dim numRows As Long
Dim i As Long

' Prompt the user to input the row number where rows will be inserted
rowNum = Application.InputBox("Enter the row number where rows will be inserted:", Type:=1)

' Check if the user entered a valid row number
If rowNum <= 0 Then
MsgBox "Invalid row number. Exiting..."
Exit Sub
End If

' Prompt the user to input the number of rows to insert
numRows = Application.InputBox("Enter the number of rows to insert:", Type:=1)

' Check if the user entered a valid number
If numRows <= 0 Then
MsgBox "Invalid number of rows. Exiting..."
Exit Sub
End If

' Loop through each worksheet in the workbook
For Each ws In ThisWorkbook.Worksheets
' Insert the specified number of rows at the specified row number
For i = 1 To numRows
ws.Rows(rowNum).Insert Shift:=xlDown
Next i
Next ws

MsgBox numRows & " rows inserted at row " & rowNum & " on each sheet."
End Sub

If you try adding new rows where cells are merged, you need to check if your codes work correctly. As I have tested, adding rows within the merged cells makes cells stay merged, while adding new rows extends those merged cells. Let’s say three cells are merged, and you insert two more rows within the merged cells. Now, you will have five merged cells after adding new rows. But I’d still suggest you test your code before dealing with merged cells.

Insert a Row in an Excel Table

If your data is in an Excel table, use the ListRows.Add method. It adds the row only to the table, and the table’s formulas and formatting carry on into the new row. Leave out the position to add the row at the end of the table:

Sub insert_table_row()
Dim tbl As ListObject
Set tbl = Worksheets("Sheet1").ListObjects(1)
tbl.ListRows.Add Position:=2
End Sub

Insert Many Rows Faster

Inserting rows one by one in a loop is slow when you insert hundreds of rows. When the rows go in one place, insert them all in one go with Resize, and turn off screen updating while the code runs:

Sub insert_many_rows()
Application.ScreenUpdating = False
Rows(5).Resize(500).Insert
Application.ScreenUpdating = True
End Sub

This inserts 500 rows above row 5 with one line of code.

Frequently Asked Questions

How do I insert a row below a cell?

Insert always adds the new row above the row you refer to. So, to add a row below the active cell, use ActiveCell.Offset(1, 0).EntireRow.Insert.

Why do I get error 1004 when inserting a row?

Either the sheet is protected without the “Insert rows” option, or there’s data in the last row of the worksheet (row 1,048,576), which Excel can’t push down. Unprotect the sheet or clear the cells at the bottom of the sheet.

Why does my loop skip rows when inserting?

Each new row pushes the rows below it down, so a loop that goes from top to bottom ends up on the wrong rows. Loop from the last row up to the first with Step -1, as in the “Insert Rows at Every Nth Row” code above.

Puneet Gogia

Written by

Puneet Gogia

Microsoft MVP — Excel · Founder, Excel Champs

Twelve years of teaching Excel, and a decade before that using it as a data analyst. Every tutorial here is written from a file I actually built.

3M+ readers helped
1,000+ tutorials written
Since 2015 teaching Excel