VBA Insert Row

Excel VBA Insert Row

As there are two things in this word one is VBA and other is Insert Row. In this, I’ll be explaining how to Insert Row in Excel using VBA (Visual Basic for Applications).

VBA – It’s a programming language for those who work in Excel and other Office programs, so one can automate tasks in Excel by writing so-called Macros. Using VBA coding we can perform all most all the tasks that we perform in Excel as we can copy, paste, delete, etc.

Insert Row – It is used to insert a row in Excel between another row with the help of automation i.e. on one click.

Suppose we are entering the Employee details in Excel and we have skipped some of the Employees and want to insert the details of the missing ones in between so we have to create a space and insert a row to enter their details. Instead of inserting row one by one we can automate the task of inserting rows using VBA and creating Macros.

There are many ways using which we can insert the rows using VBA coding. The function used for inserting a row in VBA is:

Range().Row().EntireRow.Insert

Let’s consider we have a dataset which consists of columns like Customer Name, Product, Sales, Quantity, Shipping Cost, Order Priority.

Suppose on the chance that we need to shift the columns down to insert a 3 new row between 3 and 4 to insert a new missing record. Instead of inserting row one by one we will automate this to insert all the 3 rows on a single click.

How to Insert Rows in Excel VBA?

Below are the different examples to insert rows in Excel using VBA code.

Example #1 – Using Entire Row

Follow the below steps to insert Rows in excel using VBA code.

Step 1: To create a Macro we need to select the Developer Tab.

Step 2: After the Developer tab opens, insert a button from the Controls group. Click on Insert and select the first option from ActiveX Controls. As you can see that Command Button.

Step 3: Drag the arrow at any cell to create a Command Button.

Step 4: To enter the Insert function Right-click on the Comand Button i.e. Insert 3 Rows and click on View Code.

Step 5: When you click on the View code, the following code appears.

Code:

Private Sub CommandButton1_Click()

End Sub

Step 6: We have to write our code of INSERT ROW. As mentioned previously we have to use Rows.EntireRow function with RANGE in VBA. In this code, we have to insert 3 between 3^rd and 4^th.

Code:

Private Sub CommandButton1_Click()

Range("A3").rows("3:5").EntireRow.Insert

End Sub

In the above line of code, A3 is a column where we have to insert row and Rows(3:5) is we have to insert 3 new rows.

Step 7: Run the code by clicking on the Insert 3 Rows Command Button.

Example #2 – Using Row Numbers

Similarly, we will write a code to insert 2 rows on a click of a button.

In this, we need to mention row from start row to end row number using RANGE followed by INSERT which will insert the rows.

Code:

Private Sub CommandButton2_Click()

Range("3:4").Insert

End Sub

When we click on the button “Insert 2 Rows” we see that 2 rows have been inserted in between 3^rd and 4^th row.

Similarly, we can customize our function to insert as many rows as we can.

Example #3 – Using Active Cell

Active cell means the cell that is presently selected. We will use Active cell to insert rows. Assume we are on cell B3 and want to insert the cell above it we will use active cell property. We will use the following code for the active cell.

Code:

Private Sub CommandButton3_Click()

ActiveCell.EntireRow.Insert

End Sub

When we click on the button “Active Cell Property” we see that one cell is inserted above the selected cell. In the above, we have selected B5 and below we can see that one row is inserted above it.

Example #4 – Using Active Cell with Offset

Suppose we want to insert a row after 3 rows of the active cell, for this we will use OFFSET function. Suppose we are in cell B4 and have to insert row after 2 rows, we will use this code.

Code:

Private Sub CommandButton4_Click()

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

End Sub

When we click on the button “Active Cell using Offset” one row is inserted in B6.

Things to Remember

• It is used to insert rows in Excel by Automating it.
• VBA Insert Row is used with RANGE object in VBA.
• Before working do enable the “Enable all Macros” in Macro settings.

Recommended Articles

This is a guide to VBA Insert Row. Here we discuss how to insert Row in Excel Using VBA code along with practical examples and downloadable excel template. You may also look at the following articles to learn more –

1. VBA GetObject
2. VBA StrConv
3. VBA AND
4. VBA Get Cell Value