Inserting a row in VBA somewhat differs from inserting a column in VBA. In columns, we used the entire column method to insert rows and the 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 almost all our actions in Excel with VBA coding. For example, we can copy, paste, delete, and do many more things through the VBA language. “Inserting Row” is one such method we often do in Excel. This article will show you how to perform the insert row method in VBA.
Table of contents
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, we need to use a similar technique in the Excel worksheet to insert a row. In VBA, we need to use the range objectUse The Range ObjectRange is a property in VBA that helps specify a particular cell, a range of cells, a row, a column, or a three-dimensional range. In the context of the Excel worksheet, the VBA range object includes a single cell or multiple cells spread across various rows and columns. to insert the row.
For example, look at the below code.
Sub InsertRow_Example1() Range("A1").Insert End Sub
This code will move down cell A1 to the B1 and insert the only cell.
It will cause so many problems in terms of handling the data. It will just move the mentioned cell down, and all associated columns remain the same.
Method #2 – Using Entire Row Property
In the top insert row, we can 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 selecting the “Entire Row” property.
Sub InsertRow_Example2() Range("A1").EntireRow. End Sub
Step 3: After accessing the entire row property,use the insert method.
Sub InsertRow_Example2() Range("A1").EntireRow.Insert End Sub
It will insert the row above cell A1. Since A1 is the first row, it will move down the A1 cell to B1.
As you can see 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 it 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.
Sub InsertRow_Example3() Range("6:6"). End Sub
Since we have mentioned the entire row as 6:6, we need to use the Entire Row property here. Then, we can use the “INSERT” method.
Sub InsertRow_Example3() Range("6:6").Insert End Sub
It will also insert the entire row, not the single cell.
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
It 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 the Active Cell VBA property to insert rows. The active cell is nothing but a presently selected cell.
Assume you are in cell B5 and want to insert a row above. Then, you can use the ActiveCell property.
Sub InsertRow_Example4() ActiveCell.EntireRow.Insert End Sub
It 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 two rows of the active cell. We need to use the Offset functionOffset FunctionThe OFFSET function in excel returns the value of a cell or a range (of adjacent cells) which is a particular number of rows and columns from the reference point. to offset the number of rows.
Assume you are in the B5 cell.
We can use the code below if you want to insert the row after the 2nd row from the active cell.
Sub InsertRow_Example5() ActiveCell.Offset(2, 0).EntireRow.Insert End Sub
It will insert a row after the 6th row.
Insert Alternative Rows
Inserting alternative rows is what we have come across many times. For example, look at the below data image.
Now, we need to insert alternative rows. Finally, 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 article has been a guide to VBA Insert Row. Here, we learned the top 5 methods to Insert Row in Excel VBA, some practical examples, and a downloadable Excel template. Below are some useful Excel articles related to VBA: –
- VBA IntegerVBA IntegerIn VBA, an integer is a data type that may be assigned to any variable and used to hold integer values. In VBA, the bracket for the maximum number of integer variables that can be kept is similar to that in other languages. Using the DIM statement, any variable can be defined as an integer variable.
- Top 3 Methods to Insert Row in ExcelTop 3 Methods To Insert Row In ExcelThe insertion of an excel row is simply the addition of a new (blank) row to the worksheet. The insertion of a row is eased with the help of shortcuts.
- Insert Columns in Excel using VBAInsert Columns In Excel Using VBATo add any columns in VBA, we must use the insert command and the entire column statement. When inserting a single column, use a single column reference, and when adding multiple columns, use multiple column references.
- What are ListObjects in What Are ListObjects In VBA ListObject is a way of referring to the excel tables while writing the VBA code. It facilitates the user to create, delete, or format the excel tables with the help of VBA code. VBA? VBA?VBA ListObject is a way of referring to the excel tables while writing the VBA code. It facilitates the user to create, delete, or format the excel tables with the help of VBA code.