Excel Macro to Add Row to the Bottom of a Table

In this article, I’ll show you how you can develop a macro to add a new row to the bottom of a table in Excel. You’ll learn to add an empty new row to the bottom of a table, as well as to add a row with data. The overview of the code is given in the following figure.

Excel Macro to Add Row to the Bottom of a Table: 2 Ways

In this article, we’ve got a table named Table1 in an Excel worksheet that contains the names, starting salaries, and present salaries of some employees of a company called Sunflower Group.

1. Excel Macro to Add an Empty Row to the Bottom of a Table

• First, we’ll develop a Macro to add an empty row to the bottom of the table.
• To do that, open the Microsoft Visual Basic window by pressing Alt+F11.
• Then, go to the Insert tab and click on Module.

• You can use the following VBA code for this purpose:

⧭ VBA Code:

Sub Add_Empty_Row()
Dim x As Worksheet
Set x = ActiveSheet
Table_Name = InputBox("Enter the Name of the Table: ")
Dim t As ListObjectSet t = x.ListObjects(Table_Name)
t.ListRows.Add
End Sub

⧪ Note: This code creates a Macro called Add_Empty_Row.

⧭ Output:

• First of all, save the file as Excel Macro-Enabled Workbook. Then run the Macro.

• As a result, you’ll get an Input Box asking you to enter the name of the table. Here, I’ve entered Table1.

• Then, click OK. And you’ll get a new empty row added to the bottom of the table.

Read More: How to Use VBA to Insert Row in Excel

2. Excel Macro to Add a Row with Data to the Bottom of a Table

In this method, You can also add a new row filled with data to the bottom of the table. To do it, let’s insert a row filled with the data Angela Hopkins, $35,000, and $40,000 to the bottom of the table Table1.

• First, open a Module in the Microsoft Visual Basic window like the previous method.
• The VBA code will be the following:

⧭ VBA Code:

Sub Add_Row_with_Data()
Dim x As Worksheet
Set x = ActiveSheet
Table_Name = InputBox("Enter the Name of the Table: ")
Dim t As ListObject
Set t = x.ListObjects(Table_Name)
Dim NR As ListRow
Set NR = t.ListRows.Add
Dim Data() As String
Data = Split(InputBox("Enter the Data to Fill: "), ",")
For i = 0 To UBound(Data)
    NR.Range(i + 1) = Data(i)
Next i
End Sub

⧪ Note: This code creates a Macro called Add_Row_with_Data

⧭ Output:

• Again, save the file as Excel Macro-Enabled Workbook. Then run the Macro.

• As a result, you’ll get two Input Boxes. The 1st box will ask you to enter the name of the table. Here, I’ve entered Table1.

• Then, click OK. The 2nd box will ask you to enter the data you want to enter in the new row, separated by commas.
• Here, I’ve entered Angela Hopkins,35000,40000 (No space after the commas).

• Finally, click OK.
• As a result, you’ll have your data entered inside a new row at the bottom of your table.

Read More: VBA Macro to Insert Row in Excel Based on Criteria

Practice Section

We have provided a practice section on each sheet on the right side. If you want to practice, then use this section.

Things to Remember

• Here, we’ve used the ListObject and the ListRow, two very important objects of VBA.

Download Practice Workbook

Conclusion

Using these methods, you can develop Excel Macro to add a row to the bottom of a table. If you have any queries, then feel free to ask us.

Related Articles

• Insert Rows in Excel Based on Cell Value with VBA
• Macro to Insert Row and Copy Formula in Excel
• Excel VBA: Insert Row with Values