How to Insert a Row using VBA in Excel

In this tutorial, we will look at how to insert a row or a column using a VBA code in Excel. We will also explore what are the different ways to write a macro for this.

Insert a Single Row using VBA

To insert a row using a VBA code, you need to use the “Entire Row” property with the “Insert” method. With the entire row 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.

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 “Entire Row” property or type it.
4. In the end, 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. Now when you run this code, it will instantly insert a new row before cell A1.

Insert Multiple Rows

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

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

To be honest, I haven’t found this method quite useful because you need to change the range if you want to change the count of the rows.

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).EntireRow.Insert
Next i

When you run this code, it asks you to enter the number of rows that you want to add and then 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.

Insert Rows Based on the Cell Values

If you want to insert rows based on a cell value, then you can 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 where you want to add rows from cell B1.

Insert a Row without Formatting

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

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

When you run the above code, it inserts a new row before the 7^th row. Now, what happens, when you insert a row before the 7^th row that new row becomes the 7^th row, and then 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 = True