VBA Insert Row

Inserting a row in VBA is somewhat different from inserting a column in VBA, in columns we used entire column method but to insert rows we use worksheet method with the insert command to insert a row, we also provide a row reference where we want to insert another row similar to the columns.

Insert Row with VBA Excel

We can perform all most all the actions we do in excel with VBA coding. We can copy, we can paste, we can delete, and we can do many more things through VBA language. “Inserting Row” is one of such method we often do in excel. In this article, we will show you how to perform the insert row method in VBA.

VBA Insert Row

You are free to use this image on your website, templates etc, Please provide us with an attribution linkHow to Provide Attribution?Article Link to be Hyperlinked
For eg:
Source: VBA Insert Row (wallstreetmojo.com)

How to Insert Row in Excel VBA?

Below are the various methods of using VBA to insert row in excel.

Method #1 – Using the Insert Method

In VBA, to insert a row, we need to use a similar technique we have used in the excel worksheet. In VBA, we need to use the range object to insert the row.

For example, look at the below code.

Code:

Sub InsertRow_Example1()

  Range("A1").Insert

End Sub

This code will move down the cell A1 to the B1 and insert the only cell.

VBA Insert Row Example 1-3

This will cause so many problems in terms of handling the data. This will just move the mentioned cell down, and all the other associated columns remain the same.

Method #2 – Using Entire Row Property

Top insert row, we can actually use several methods. The below method will insert the entire row above the selected cell.

Step 1: Mention the cell address first.

Code:

Sub InsertRow_Example2()

  Range("A1").

End Sub
Using Entire Row 1

Step 2: Instead of just using select the “Entire Row” property.

Code:

Sub InsertRow_Example2()

  Range("A1").EntireRow.

End Sub
Using Entire Row 1-1

Step 3: After accessing the entire row property, use the insert method.

Code:

Sub InsertRow_Example2()

  Range("A1").EntireRow.Insert

End Sub
Using Entire Row 1-2

This will insert the row above the cell A1. Since A1 is the first row, it will move down the A1 cell to B1.

Using Entire Row 1-3

As you can in the above image, it has to insert the entire row, not the single cell.

Method #3 – Using Row Numbers

In the above example, we have just used the single-cell address and inserted the row. However, we can also insert them by using row numbers.

Assume you want to insert a row below the 5th row. First, we need to mention the row numbers using the RANGE object.

Code:

Sub InsertRow_Example3()

  Range("6:6").

End Sub

Since we have mentioned the entire row as 6:6, we need to use Entire Row property here. We can straight use the “INSERT” method.

Code:

Sub InsertRow_Example3()

  Range("6:6").Insert

End Sub

This will also insert the entire row, not the single cell.

Using Row Numbers 1

In case if you want to insert two rows below the 5th row, we need to select 2 rows first and then use the INSERT method.

Sub InsertRow_Example3()

  Range("6:7").Insert

End Sub

This will insert two rows below the 5th row.

Using Row Numbers 1-1

Like this, we can insert as many rows as possible in the worksheet.

Method #4 – Using Active Cell Property

We can use the Active Cell VBA property to insert rows. The active cell is nothing but a presently selected cell.

Assume you are in the cell B5, and you want to insert a row above, you can use active cell property.

Sub InsertRow_Example4()

  ActiveCell.EntireRow.Insert

End Sub

This will insert the row above the active cell.

Method #5 – Using Active Cell Property with Offset Function

Assume you want to insert a row after 2 rows of the active cell, we need to use the Offset functionOffset FunctionThe offset function in Excel displays the range of parts from the cell's start point. It takes five arguments, all of which are mandatory. This function is used in the following way: =offset(reference, rows, columns, height, width), where height and width refer to the referred reference.read more to offset the number of rows.

Assume you are in the B5 cell.

Using Active Cell with offset 1

If you want to insert the row after the 2nd row from the active cell, then we can use the below code.

Code:

Sub InsertRow_Example5()

  ActiveCell.Offset(2, 0).EntireRow.Insert

End Sub

This will insert row after the 6th row.

Using Active Cell with offset 1-1

Insert Alternative Rows

Inserting alternative rows is often I have come across many times. For the look at the below data image.

Insert Alternative Rows 1

Now we need to insert alternative rows. We need to use loops to insert every alternate row.

Code:

Sub InsertRow_Example6()
  Dim K As Integer
  Dim X As Integer

  X = 1

  For K = 1 To 4
    Cells(X, 1).EntireRow.Insert
    X = X + 2
  Next K
End Sub

This will insert rows like this.

Insert Alternative Rows 1-1

You can download this VBA Insert Row Excel here. VBA Insert Row Excel Template

Recommended Articles

This has been a guide to VBA Insert Row. Here we learned the top 5 methods to Insert Row in Excel VBA along with some practical examples and a downloadable excel template. Below are some useful excel articles related to VBA –

  • 3 Courses
  • 12 Hands-on Projects
  • 43+ Hours
  • Full Lifetime Access
  • Certificate of Completion
LEARN MORE >>