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.
How to Insert Row in Excel VBA?
Below are the various methods of using VBA to insert a 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 excel worksheet. In VBA we need to use the range object to insert the row.
For example, look at the below 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.
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.
Sub InsertRow_Example2() Range("A1"). End Sub
Step 2: Instead of just using select the “Entire Row” property.
Sub InsertRow_Example2() Range("A1").EntireRow. End Sub
Step 3: After accessing entire row property use the insert method.
Sub InsertRow_Example2() Range("A1").EntireRow.Insert End Sub
This will insert the row above the cell A1. Since A1 is the first row it will move down the A1 cell to B1.
4.6 (247 ratings) 3 Courses | 12 Hands-on Projects | 43+ Hours | Full Lifetime Access | Certificate of Completion
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 RANGE object.
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.
Sub InsertRow_Example3() Range("6:6").Insert End Sub
This will also insert the entire row not the single cell.
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.
Like this, we can insert as many rows as possible in the worksheet.
Method #4 – Using Active Cell Property
We can use 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 Offset function to offset the number of rows.
Assume you are in the B5 cell.
If you want to insert the row after the 2nd row from the active cell then we can use the below code.
Sub InsertRow_Example5() ActiveCell.Offset(2, 0).EntireRow.Insert End Sub
This will insert row after the 6th row.
Insert Alternative Rows
Inserting alternative rows is often I have come across many times. For the look at the below data image.
Now we need to insert alternative rows. We need to use loops to insert every alternate row.
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.
You can download this VBA Insert Row Excel here. VBA Insert Row Excel Template
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 downloadable excel template. Below are some useful excel articles related to VBA –