VBA Insert Row

What is Insert Row with VBA Excel?

VBA Insert Row is a programming technique that dynamically adds new rows to a worksheet in Microsoft Excel. Using VBA insert Row, you can write code to insert new rows at specific locations in the worksheet, such as at the end of a table, above or below a selected cell, or based on certain conditions.

Let us look at an example. In this example, a macro named “InsertRowWithValues” uses VBA Insert Row below the last used row in an Excel worksheet. The macro populates the new row with specific values “John,” “Doe,” “25,” and “Engineer” from an array. When executed, the macro dynamically adds the row and fills it with the predefined values.

The output is a new row with the values “John,” “Doe,” “25,” and “Engineer” inserted below the last used row in the worksheet as seen below.

How to Insert Row in Excel VBA? (with Steps and examples)

Here are five methods for VBA Insert Row:

Method #1 – Using the Insert Method

Example – Auto-Inserting a New Row on Data Entry

Suppose you have a data entry form in Excel and want to automatically code a VBA Insert Row above the last entry every time a new data point is entered.

Step 1: In a new Excel workbook, press “ALT + F11” in Excel to open the Visual Basic for Applications (VBA) editor.

Step 2: In the VBA Editor, you create a new module to write your code. It can be done by selecting Insert from the menu and then choosing Module.

Step 3: In the new module, start by creating a new subroutine, “AutoInsertRowUsingInsertMethod().”

Step 4: Next, we declare a variable named “lastRow” of the data type “Long.” A Long variable is used to store whole numbers.

Step 5: Here, we use this code that finds the last used row in Column A (assuming the data starts in Column A). It counts the total number of rows in the worksheet (Rows.Count), then starts from the last row and moves upwards (End(xlUp)) until it encounters the first non-empty cell in Column A. The row number of the last non-empty cell is stored in the “lastRow” variable.

Step 6: In this line, the Insert method is called on the row represented by the “lastRow” variable, which inserts a new row just above the last used row in Column A.

Step 7: Save the macro and close the VBE editor. Press “ALT + F8” to open the “Macro” dialog box.

Select the ” AutoInsertRowUsingInsertMethod ” macro from the list and click “Run.”

Step 8: Once you execute the code, you will see that VBA insert row above is triggered, and a new row is inserted just above the last used row. i.e., Row 20.

Here is the complete code:

Sub AutoInsertRowUsingInsertMethod()
Dim lastRow As Long
‘ Find the last used row in the worksheet
lastRow = Cells(Rows.Count, 1).End(xlUp).Row
‘ Insert a new row below the last used row
Rows(lastRow).Insert
End Sub

Excel VBA – All in One Courses Bundle (35+ Hours of Video Tutorials)

If you want to learn Excel and VBA professionally, then ​Excel VBA All in One Courses Bundle​ (35+ hours) is the perfect solution. Whether you’re a beginner or an experienced user, this bundle covers it all – from Basic Excel to Advanced Excel, Macros, Power Query, and VBA.

Method #2 – Using Entire Row Property

Example – Inserting Multiple Rows for Categorized Data

Suppose you have a list of expenses in Excel.

And you want to insert multiple rows for each category to provide additional details using VBA Insert Row.

Step 1: In the new module, create a subroutine named “InsertMultipleRowsForEachCategory.”

Step 2: In the following line, we declare a variable named “categoryRow” of the data type “Long.” A Long variable is used to store whole numbers. This variable will track the row number where each category is listed.

Step 3: Here, we declare a variable named “i” of the data type “Long.” The variable “i” will be used as a loop counter to insert multiple rows for each category.

Step 4: In the next line, we assign “categoryRow = 1.” This line initializes the “categoryRow” variable with the value 1. It assumes the categories are listed starting from row 1 in Column A.

Step 5: Now, we will declare a variable named “numRowsToInsert” and assign it a value of 3. It means that we want to insert three rows for each category.

Step 6: We use the “Do While” VBA loop in this line of code. This line starts a “Do While” loop until an empty cell is encountered in Column A (end of categories).

Step 7: For i = 1 To numRowsToInsert: This line starts a “For” loop that iterates from 1 to the value of “numRowsToInsert” (which is 3). This loop will insert three rows for each category.

Step 8: In this line, the Insert method is called on the row represented by “categoryRow + 1,” which inserts a new row below the current category row (categoryRow). It will create additional rows for the same category.

Step 9: This line updates the “categoryRow” variable to move to the next category row. It adds the value of “numRowsToInsert” and 1 to skip to the next category row (one row below the last inserted row for the current category).

Step 10: This line indicates the end of the “Do While” loop. The loop will repeat until an empty cell is encountered in Column A.

Step 11: Save the macro and close the VBE editor. Press “ALT + F8” to open the “Macro” dialog box.

Select the ” InsertMultipleRowsForEachCategory” macro from the list and click “Run.”

Step 12: Once you execute the VBA code, you will see that the new rows are added for each expense item that was already existing.

Here is the complete code:

Sub InsertMultipleRowsForEachCategory()
Dim categoryRow As Long
Dim i As Long
Dim numRowsToInsert As Long
‘ Assume the categories start in Column A, and data starts in Column B
categoryRow = 1
numRowsToInsert = 3 ‘ Insert 3 rows for each category
Do While Cells(categoryRow, 1).Value <> “”
For i = 1 To numRowsToInsert
Rows(categoryRow + 1).Insert
Next i
categoryRow = categoryRow + numRowsToInsert + 1 ‘ Move to the next category
Loop
End Sub

Method #3 – Using Row Numbers

Example – Inserting a New Row at the End of Data Table

Suppose you have a data table in Excel and want to VBA Insert Row below to enter the latest data.

Step 1: In the new module, create a subroutine named “InsertRowAtEndOfDataTable.”

Step 2: In the next line, we declare a variable named “lastRow” of the data type “Long.” A Long variable is used to store whole numbers.

Step 3: Next, we will declare another variable named “newRow” of the data type “Long.” The variable “newRow” will store the row number for inserting the new row.

Step 4: We code “lastRow = Cells ” (Rows.Count, 1).End(xlUp).Row.” This line finds the last used row in Column A (assuming the data starts in Column A). It counts the total number of rows in the worksheet (Rows.Count), then starts from the last row and moves upwards (End(xlUp)) until it encounters the first non-empty cell in Column A. The row number of the last non-empty cell is stored in the “lastRow” variable.

Step 5: Next, we write “newRow = lastRow + 1.” This line calculates the row number for the new row to be inserted. It adds 1 to the “lastRow” variable to get the row number immediately below the last used row in Column A.

Step 6: In this step, the Insert method is called on the row represented by the “newRow” variable, which inserts a new row at the end of the data table (below the last used row in Column A).

Step 7: Save the macro and close the VBE editor. Press “ALT + F8” to open the “Macro” dialog box.

Select the ” InsertRowAtEndOfDataTable ” macro from the list and click “Run.”

Step 8: Once you execute the code, you will see that VBA Insert Row below is triggered, and a new row is inserted at the end of the data table.

Here is the complete code:

Sub InsertRowAtEndOfDataTable()
Dim lastRow As Long
Dim newRow As Long
‘ Find the last used row in the worksheet
lastRow = Cells(Rows.Count, 1).End(xlUp).Row
‘ Calculate the row number for the new row (one row below the last used row)
newRow = lastRow + 1
‘ Insert a new row at the end of the data table
Rows(newRow).Insert
End Sub

Method #4 – Using Active Cell Property

Example – Dynamic Row Insertion based on User Input

Suppose you want to allow users to insert a new row above the active cell by running a macro.

Step 1: In the new module, create a subroutine named “DynamicRowInsertionUsingActiveCell.”

Step 2: Next, we declare a variable named “rng” of the data type “Range.” A range variable stores a reference to a specific cell or range of cells in the worksheet.

Step 3: Here, we declare another variable named “newRow” of the data type “Long.” The variable “newRow” will be used to store the row number for the new row to be inserted.

Step 4: Next, we will assign the active cell in VBA (currently selected by the user) to the variable “rng.”

Step 5: In the next line, we write the code “If Not Intersect(rng, ActiveSheet.UsedRange) Is Nothing Then.” This line checks if the active cell is within the worksheet’s used range (i.e., not outside the worksheet).

Step 6: Here, we retrieve the active cell’s row number and store it in the “newRow” variable.

Step 7: In this line, the Insert method is called on the row represented by the “newRow” variable, which inserts a new row above the active cell.

Step 8: Next, we use “Else.” This line is part of the “If-Then” structure and is executed if the condition in line 5 is not met (i.e., the active cell is outside the worksheet).

Step 9: Next, we use the MsgBox function with a short statement, “Please select a valid cell within the worksheet.” This line displays a message box with the specified text, informing the user to select a valid cell within the worksheet.

Step 10: We end the code with “End If.” This line indicates the end of the “If-Then” structure.

Step 11: Save the macro and close the VBE editor. Press “ALT + F8” to open the “Macro” dialog box.

Select the ” DynamicRowInsertionUsingActiveCell ” macro from the list and click “Run.”

Step 12: When you select an active cell, (in this example Cell A2) and execute the code, you will see that the new row is inserted just above the select active cell.

You can also achieve the new row creation below the active cell using VBA Insert Row below active cell property.

Here is the complete code:

Sub DynamicRowInsertionUsingActiveCell()
Dim rng As Range
Dim newRow As Long
‘ Get the active cell
Set rng = ActiveCell
‘ Check if the active cell is in a valid range (not outside the worksheet)
If Not Intersect(rng, ActiveSheet.UsedRange) Is Nothing Then
‘ Get the row number of the active cell
newRow = rng.Row
‘ Insert a new row above the active cell
Rows(newRow).Insert
Else
MsgBox “Please select a valid cell within the worksheet.”
End If
End Sub

Method #5 – Using Active Cell Property with Offset Function

Example – Auto-Inserting a New Row with Formulas

Suppose you have a budget spreadsheet in Excel.

And you want to automatically do a VBA Insert Row with values below the active cell with formulas to calculate the totals.

Step 1: In the new module, we start with a new subroutine named “AutoInsertRowWithFormulas.”

Step 2: Here, we declare a variable named “newRow” of the VBA data type “Range.” A range variable is used to store a reference to a specific cell or range of cells in the worksheet.

Step 3: In this line, we use the “Offset” excel function on the active cell to move one row down (Offset(1)). The “EntireRow” property is then applied to the resulting cell, which represents the entire row below the active cell. The entire row is assigned to the variable “newRow.”

Step 4: In this line of code, the Insert method is called on the row represented by the “newRow” variable, which inserts a new row below the active cell.

Step 5: Here, we write the code “newRow.Cells(1, 3).Formula = “=SUM(C2:C” & newRow.Row – 1 & “).” This line sets a formula in the new row to calculate the sum of values in column C. It uses the “Formula” property to assign the formula to the new row’s first cell in column C. The formula uses the “SUM” function to sum the values in the range from “C2” to the cell immediately above the new row.

Step 6: In this line, we will set another formula in the new row to calculate the sum of values in column D. It follows a similar structure to the previous formula.

Step 7: If required, add more lines like step 5 and 6 to set formulas for other columns as needed.

Step 8: Save the macro and close the VBE editor. Press “ALT + F8” to open the “Macro” dialog box.

Select the ” AutoInsertRowWithFormulas” macro from the list and click “Run.”

Step 9: Once you execute the macro, you will see that the new cells are created with formulas.

Here is the full code:

Sub AutoInsertRowWithFormulas()
Dim newRow As Range
‘ Get the active cell
Set newRow = ActiveCell.Offset(1).EntireRow
‘ Insert a new row below the active cell
newRow.Insert
‘ Set formulas for the new row to calculate totals
newRow.Cells(1, 3).Formula = “=SUM(C2:C” & newRow.Row – 1 & “)” ‘ Total for column C
newRow.Cells(1, 4).Formula = “=SUM(D2:D” & newRow.Row – 1 & “)” ‘ Total for column D
‘ Add more formulas for other columns as needed
End Sub

Important Things To Note

1. When you apply the VBA insert row, existing rows get shifted down, so no data is lost.
2. Be mindful of handling errors correctly, especially when dealing with dynamic values such as active cells or variable row numbers.
3. If inserting multiple rows using VBA Insert Row, consider disabling screen updating (Application.ScreenUpdating = False) to speed up code execution.

Frequently Asked Questions (FAQs)

Download Template

This article must be helpful to understand the VBA Insert Row, with its formula and examples. You can download the template here to use it instantly.

Recommended Articles

Guide to VBA Insert Row. Here we learn to insert row in excel using five different methods with examples & downloadable excel template. You can learn more from the following articles –

• VBA Row Count
• VBA Delete Row
• VBA Last Row