Looking for ways to insert row with values using VBA in Excel? Then, this is the right place for you.
We occasionally need to modify our large dataset in Excel by inserting rows based on specific requirements. Using a VBA macro is the most efficient, fastest, and safest way to run any operation in Excel. If you are looking for special tricks to insert rows with values using VBA in Excel, you’ve come to the right place. There are numerous ways to insert rows with values using VBA in Excel. These techniques for inserting rows with values using VBA in Excel will be covered in detail in this article.
Below, we use VBA code to insert rows with values in Excel.
Insert Row with Values: 4 Examples
In the following section, we will use four effective and tricky methods to insert rows with values using Excel VBA. This is Microsoft’s event-driven programming language called Visual Basic for Applications (VBA). To use this feature, you first need to enable the Developer tab on the ribbon. This section provides extensive details on these methods. You should learn and apply these to improve your thinking capability and Excel knowledge. We use the Microsoft Office 365 version here, but you can utilize any other version according to your preference.
1. Insert Single Row with Values
In this case, we’ll use an Excel VBA code to insert a single row with values into a dataset that contains student names and grades. The outcome will resemble the picture shown above. The code below has been used to accomplish that.
Sub Insert_single_row()
Rows("7:7").Select
Selection.Insert Shift:=xlDown, CopyOrigin:=xlFormatFromLeftOrAbove
Cells(7, 2).Value = "Ross"
Cells(7, 3).Value = "Chemistry"
cells (7, 4).Value = "86"
End Sub
🔎Code Explanation:
Sub Insert_single_row()
First of all, provide a name for the sub-procedure of the macro.
Rows("7:7").SelectThis code selects the 7th row in the worksheet.
Selection.Insert Shift:=xlDown, CopyOrigin:=xlFormatFromLeftOrAboveThe existing rows are shifted downward and a new row is inserted below the currently selected row by this code.
Cells(7, 2).Value = "Ross"
Cells(7, 3).Value = "Chemistry"
Cells (7, 4).Value = "86"This code sets the values “Ross,” “Chemistry,” and “86” for the cells in the second, third, and fourth columns of the seventh row.
End SubFinally, end the sub-procedure of the macro.
By pressing F5 at this point or by selecting the Macro window from the Developer tab, you can run the code. The results will therefore be as follows.
Read More: How to Use VBA to Insert Row in Excel
2. Insert Multiple Rows with Values
Here, we’ll use an Excel VBA code to add multiple rows with values in our Excel dataset. The result will look similar to the image above. To do that, the following code has been employed.
Sub insert_mutliple_rows()
Dim Number_of_rows As Integer
Dim Starting_row As Integer
Number_of_rows = 3
Starting_row = 7
For i = 1 To Number_of_rows
Rows(Starting_row).Insert Shift:=xlDown
Range("B" & Starting_row).Value = "Ross"
Range("C" & Starting_row).Value = "Math"
Range("D" & Starting_row).Value = "88"
Starting_row = Starting_row + 1
Next i
End Sub
🔎Code Explanation:
Sub insert_multiple_rows()First of all, provide a name for the sub-procedure of the macro.
Dim Number_of_rows As Integer
Dim Starting_row As IntegerNext, declare the necessary variable for the macro.
Number_of_rows = 3
Starting_row = 7This code set the values of Starting_row and Number_of_rows to 3 and 7, respectively.
For i = 1 To Number_of_rows
Rows(Starting_row).Insert Shift:=xlDownThis code moves the current rows down and inserts a new row beneath the row indicated by Starting_row.
Range("B" & Starting_row).Value = "Ross"
Range("C" & Starting_row).Value = "Math"
Range("D" & Starting_row).Value = "88"These code change the values of cells B, C, and D in the Starting_row row to “Ross,” “Math,” and “88,” respectively.
Starting_row = Starting_row + 1
Next iThis line increases the value of Starting_row by one so that the next loop iteration inserts a new row below the next row.
End SubFinally, end the sub-procedure of the macro.
You can run the code at this point by pressing F5 or by choosing the Macro window from the Developer tab.
As a result, the following output will appear after three rows have been added.
Read More: Insert Rows in Excel Based on Cell Value with VBA
3. Insert Row in a Table with Data
Using Excel VBA, we will insert a row in a table with data in this method. Three examples are provided here: Insert a row with data at the bottom, insert a row at a specific position, and insert a row based on user input.
3.1 Add Row to the Bottom With data
Here, we’ll use an Excel VBA code to add a row to the bottom of our Excel dataset. Similar to the image above, the outcome will appear. This has been accomplished using the following code.
Sub Add_Row_to_Bottom_With_data()
Dim Name_of_table As ListObject
Set Name_of_table = ActiveSheet.ListObjects("Table1")
Dim adding_row As ListRow
Set adding_row = Name_of_table.ListRows.Add()
With adding_row
.Range(1) = "David"
.Range(2) = "Chemistry"
.Range(3) = "94"
End With
End Sub
🔎 Code Explanation:
Sub Add_Row_to_Bottom_With_data()First of all, provide a name for the sub-procedure of the macro.
Dim Name_of_table As ListObject
Set Name_of_table = ActiveSheet.ListObjects("Table1")This code sets the “Name_of_table” variable to the “Table1” table on the active worksheet and declares it to be a ListObject type.
Dim adding_row As ListRow
Set adding_row = Name_of_table.ListRows.Add()The “Add” method of the ListRows collection is used to add a new row to the table named “Name_of_table” in the code.
With adding_row
.Range(1) = "David"
.Range(2) = "Chemistry"
.Range(3) = "94"
End WithThis code sets the values for the first three columns of the newly added row.
End SubFinally, end the sub-procedure of the macro.
You can run the code at this point by pressing F5 or by choosing the Macro window from the Developer tab.
As a result, after adding a row to the bottom of the table, the following output will appear.
3.2 Insert Row at a Specific Position
In this section, we’ll use Excel VBA code to insert a row with values at a specific location in our Excel dataset. The result will look like the image above. The following code was used to accomplish this.
Sub Insert_Row_at_Specific_Position()
Dim Name_of_Table As ListObject
Set Name_of_Table = ActiveSheet.ListObjects("Table13")
Dim adding_row As ListRow
Set adding_row = Name_of_Table.ListRows.Add(3)
With adding_row
.Range(1) = "Ricky"
.Range(2) = "Biology"
.Range(3) = "83"
End With
End Sub
🔎Code Explanation:
Sub Insert_Row_at_Specific_Position()First of all, provide a name for the sub-procedure of the macro.
Dim Name_of_Table As ListObject
Set Name_of_Table = ActiveSheet.ListObjects("Table13")This code sets the “Name_of_Table” variable to the “Table13” table on the active worksheet and declares it to be a ListObject type.
Dim adding_row As ListRow
Set adding_row = Name_of_Table.ListRows.Add(3)The “Add” method of the ListRows collection is used to add a new row to the table named “Name_of_Table” in the code.
With adding_row
.Range(1) = "Ricky"
.Range(2) = "Biology"
.Range(3) = "83"
End WithThis code sets the values for the first three columns of the newly added row.
End SubFinally, end the sub-procedure of the macro.
You can run the code at this point by pressing F5 or by choosing the Macro window from the Developer tab.
As a result, after adding a row to a specific position of the table, the following output will appear.
3.3 Insert Row based on User Input
We’ll insert a row with value into our Excel dataset based on user input in this section using Excel VBA code. The end result will resemble the picture above. To do this, the following code was used.
Sub Insert_Row_based_on_user_Input()
Dim Name_of_Table As ListObject
Dim A, B, C, D, E, T1_Name As String, Msg As String
On Error GoTo Message
T1_Name = InputBox("Enter Name of the Table: ", "Table Name")
Set Name_of_Table = ActiveSheet.ListObjects(T1_Name)
If T1_Name = "" Then Exit Sub
A = InputBox("Enter Student Name: ", "Name of Student")
If A = "" Then Exit Sub
B = InputBox("Enter Course Name: ", "Course")
If B = "" Then Exit Sub
C = InputBox("Enter Marks: ", "Marks")
If C = "" Then Exit Sub
Dim addedRow As ListRow
Set addedRow = Name_of_Table.ListRows.Add()
With addedRow
.Range(1) = A
.Range(2) = B
.Range(3) = C
End With
Exit Sub
Message:
Msg = "Your table was Not found."
MsgBox Msg, vbCritical
End Sub
🔎Code Explanation:
Sub Insert_Row_based_on_user_Input()First of all, provide a name for the sub-procedure of the macro.
Dim Name_of_Table As ListObject
Dim A, B, C, D, E, T1_Name As String, Msg As StringNext, declare the necessary variable for the macro.
On Error GoTo MessageThis line will jump to the Message label if an error occurs.
On Error GoTo Messageer Name of the Table: ", "Table Name")Prompts the user to provide the name of the Excel table they wish to add a row to in an input box.
Set Name_of_Table = ActiveSheet.ListObjects(T1_Name)Assigns the Excel table whose name is supplied in the T1 Name variable to the Name of Table variable.
If T1_Name = "" Then Exit SubVerifies whether a table name was entered into the input field. If not, the subroutine will terminate.
A = InputBox("Enter Student Name: ", "Name of Student")
If A = "" Then Exit Sub
B = InputBox("Enter Course Name: ", "Course")
If B = "" Then Exit Sub
C = InputBox("Enter Marks: ", "Marks")
If C = "" Then Exit SubThese lines show an input box that asks the user to submit the necessary data, and then check if they entered anything. If they did not, the subroutine will exit.
Dim addedRow As ListRow
Set addedRow = Name_of_Table.ListRows.Add()Create a new ListRow object variable called addedRow, set it to a new row in the Excel table identified by the Name_of_Table variable.
With addedRow
.Range(1) = A
.Range(2) = B
.Range(3) = C
End WithThe user-inputted values for the A, B, and C variables are used to set the values of the first three cells in the added row object.
Exit SubThis line ends the subroutine.
Message:
Msg = "Your table was Not found."
MsgBox Msg, vbCriticalIf an error occurs in the subroutine, this code block is jumped to.
End SubFinally, end the sub-procedure of the macro.
At this point, you can run the code by pressing F5, or you can select the Macro window from the Developer tab.
The output will therefore look as follows after adding a row based on user input.
Read More: VBA Macro to Insert Row in Excel Based on Criteria
4. Insert Rows with Values Using User Form
In this section, using Excel VBA code, we’ll insert row with values to our Excel dataset in response to user input. The final outcome will look similar to the image above. To do this, follow the below steps.
Create UserForm:
- First, from the Visual Basic Editor click on UserForm from the Insert drop-down menu bar.
- This will create a UserForm in your Visual Basic Editor.
- We add two labels to this user form to create it. One is named Student Name, and the other is Marks.
- Then we insert a CommandButton named Add.
- Therefore, we will get the following UserForm.
Macro of Add Button:
- Next, double-click on the Add button and type the following Code.
Private Sub CommandButton1_Click()
Dim StudentName As String
StudentName = txtstudent.Text
Dim Marks As String
Marks = txtMarks.Text
If StudentName = "" Or Marks = "" Then
MsgBox "Please Enter both Student Name and Marks", vbExclamation, "Empty fields"
Exit Sub
End If
Dim Wsh As Worksheet
Set Wsh = ThisWorkbook.Worksheets("Sheet8")
Set tb1 = Wsh.ListObjects("Table3")
Dim L1row As ListRow
Set L1row = tb1.ListRows.Add
With L1row
.Range(1) = StudentName
.Range(2) = Marks
End With
End Sub
🔎Code Explanation:
Private Sub CommandButton1_Click()This line defines the sub-procedure.
Dim StudentName As String
StudentName = txtstudent.TextThe text entered in the “txtstudent” text box is used to create a string variable called “StudentName” and assign it a value.
Dim Marks As String
Marks = txtMarks.TextAnother variable called “Marks” is declared in the code to be a string, and its value is the text that was entered into the “txtMarks” text box.
If StudentName = "" Or Marks = "" Then
MsgBox "Please Enter both Student Name and Marks", vbExclamation, "Empty fields"
Exit Sub
End IfChecks to see if the “StudentName” or “Marks” variables are empty, and if they are, it displays a message box asking the user to enter the student’s name and marks.
Dim Wsh As Worksheet Set Wsh = ThisWorkbook.Worksheets("Sheet8")The “Sheet8” worksheet from the current workbook is set as the variable “Wsh,” which is declared in the code as a worksheet.
Set tb1 = Wsh.ListObjects("Table3")Creates a variable called “tb1” that is declared to be a list object.
Dim L1row As ListRow
Set L1row = tb1.ListRows.AddDeclares a variable named “L1row” as a list row and adds a new row to the list object
With L1row
.Range(1) = StudentName
.Range(2) = Marks
End WithWe assigned the first column of the new row as the value of the “StudentName” variable, and “Marks” variable is value the second column.
End SubFinally, end the sub-procedure of the macro.
Insert Code in Module to Show UserForm:
- After that, insert a new module and type the following code.
Sub ShowForm()
UserForm1.Show
End Sub
Assign Code in Button 1:
Here, we insert Button 1 to assign the Userform Show code. The outcome will look similar to the image above.
- You can now execute the code by selecting Button 1.
- UserForm1 will consequently appear.
- Then, enter the student’s name and marks and select Add.
- Finally, you will be able to insert a row as shown below.
How to Insert Alternate Rows with Excel VBA
Using Excel VBA code, we’ll add alternative rows to our dataset in Excel. The final outcome will look similar to the image above. To do this, we used the following code.
Sub Insert_alternate_Rows()
Dim rng1 As Range
Set rng1 = Selection
Dim i As Long
For i = rng1.Rows.Count To 1 Step -1
rng1.Rows(i).EntireRow.Insert
Next i
End Sub
🔎Code Explanation:
Sub Insert_alternate_Rows()First of all, provide a name for the sub-procedure of the macro.
Dim rng1 As Range
Dim i As LongNext, declare the necessary variable for the macro.
Set rng1 = Selection
For i = rng1.Rows.Count To 1 Step -1
rng1.Rows(i).EntireRow.Insert
Next iIn general, we used this code to insert a blank row following each set of rows.
End SubFinally, end the sub-procedure of the macro.
Pressing F5 or choosing the Macro window from the Developer tab will now allow you to execute the code.
Therefore, after adding alternate rows, the output will appear as follows.
How to Insert Row with Formatting and Formula with Excel VBA
Here, we’ll use an Excel VBA code to insert rows with formatting and formulas in our Excel dataset. The result will look similar to the image above. To do that, we used the following code.
Sub Insert_Rows_with_Formatting_and_Formula()
Number_of_Rows = Int(InputBox("Enter the Number of Rows to Insert: "))
For i = 1 To Number_of_Rows
Selection.EntireRow.Insert , xlFormatFromLeftOrAbove
Next i
Range(Cells(ActiveCell.Row + Number_of_Rows, ActiveCell.Column), Cells(ActiveCell.Row + Number_of_Rows, ActiveCell.Column)).Copy
Range(Cells(ActiveCell.Row, ActiveCell.Column), Cells(ActiveCell.Row, ActiveCell.Column)).PasteSpecial Paste:=xlPasteFormulas
For i = 1 To Number_of_Rows - 1
Range(Cells(ActiveCell.Row + 1, ActiveCell.Column), Cells(ActiveCell.Row + 1, ActiveCell.Column)).PasteSpecial Paste:=xlPasteFormulas
Next i
Application.CutCopyMode = False
End Sub
🔎Code Explanation:
Sub Insert_Rows_with_Formatting_and_Formula()First of all, provide a name for the sub-procedure of the macro.
Number_of_Rows = Int(InputBox("Enter the Number of Rows to Insert: "))This code prompts the user to insert the number of rows to insert.
For i = 1 To Number_of_Rows
Selection.EntireRow.Insert , xlFormatFromLeftOrAbove
Next iThis loop begins with the active cell and adds the specified number of rows to the worksheet.
Range(Cells(ActiveCell.Row + Number_of_Rows, ActiveCell.Column), Cells(ActiveCell.Row + Number_of_Rows, ActiveCell.Column)).Copy
Range(Cells(ActiveCell.Row, ActiveCell.Column), Cells(ActiveCell.Row, ActiveCell.Column)).PasteSpecial Paste:=xlPasteFormulasThis line of code picks out the active cell and pastes the formula from the copied cell into it.
For i = 1 To Number_of_Rows - 1
Range(Cells(ActiveCell.Row + 1, ActiveCell.Column), Cells(ActiveCell.Row + 1, ActiveCell.Column)).PasteSpecial Paste:=xlPasteFormulas
Next i
Application.CutCopyMode = FalseThis loop will copy the formula from the active cell and paste into the remaining inserted rows.
End SubFinally, end the sub-procedure of the macro.
You can run the code at this point by pressing F5 or by choosing the Macro window from the Developer tab.
As a result, the following output will appear.
Read More: Macro to Insert Row and Copy Formula in Excel
Frequently Asked Questions
1. How do I insert a row based on a cell value in VBA?
Sometimes, you need to insert a row based on a cell value in Excel. To do the task, you have to use the following code.
Sub Inserting_Row_Based_On_Cell_Value()
Dim rng1 As Range
Dim cell As Range
Set rng1 = Range("A1:D10")
For Each cell In rng1
If cell.Value = "InsertRowHere" Then
cell.Offset(1, 0).EntireRow.Insert
End If
Next cell
End SubYou can run the code at this point by pressing F5 or by choosing the Macro window from the Developer tab.
As a result, the following output will appear after you added a row based on a cell value “ InsertRowHere”.
2. How do I insert a value in VBA?
In Excel, you may occasionally need to enter a value into a cell. You need to use the following code to complete the task.
Sub insertvalue()
Dim myValue As Integer
myValue = 10
Range("A2").Value = 10
End SubPressing F5 or selecting the Macro window from the Developer tab will allow you to run the code at this point.
As a result, after you enter a value in cell D6, you will see the following output.
3. How do I insert an entire row above in VBA?
You might occasionally need to enter a row above in an Excel cell. To finish the task, you must use the outlined code.
Sub Inserting_Row_Above()
Dim rowNum1 As Integer
rowNum1 = 7
Rows(rowNum1).Insert Shift:=xlDown
End SubYou can now run the code by pressing F5 or by choosing the Macro window from the Developer tab.
Because of this, you will get the following output after adding a row.
Download Practice Workbook
Download this practice workbook to exercise while you are reading this article. It contains all the datasets in different spreadsheets for a clear understanding. Try it yourself while you go through the whole process.
Conclusion
That’s the end of today’s session. Now, I strongly believe that from now, you may be able to insert row with values using VBA in Excel. If you have any queries or recommendations, please share them in the comments section below.
Don’t forget to check our website Exceldemy, for various Excel-related problems and solutions. Keep learning new methods and keep growing!
