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.


Excel Macro to Add Row to the Bottom of a Table (Quick View)

Sub Add_Empty_Row()

Dim ws As Worksheet
Set ws = ActiveSheet

Table_Name = InputBox("Enter the Name of the Table: ")

Dim tbl As ListObject
Set tbl = ws.ListObjects(Table_Name)

tbl.ListRows.Add

End Sub

VBA Code to Develop Excel Macro to Add Row to the Bottom of a Table

Explanation of the Code:

  • This code creates a Macro called Add_Empty_Row.
  • It takes the name of a table as input from the user and then sets the table as a ListObject.
  • Then, it adds a new ListRow to the bottom of the table.

Download Practice Workbook


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

Here 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.

Data Set to Develop Excel Macro to Add Row to the Bottom of a Table

Today our objective is to develop a Macro to add a new row to the bottom of this table.


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.

You can use the following VBA code for this purpose:

VBA Code:

Sub Add_Empty_Row()

Dim ws As Worksheet
Set ws = ActiveSheet

Table_Name = InputBox("Enter the Name of the Table: ")

Dim tbl As ListObject
Set tbl = ws.ListObjects(Table_Name)

tbl.ListRows.Add

End Sub

Note: This code creates a Macro called Add_Empty_Row.

VBA Code to Develop Excel Macro to Add Row to the Bottom of a Table

Output:

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

You’ll get an Input Box asking you to enter the name of the table. Here I’ve entered Table1.

Entering Input to Develop Excel Macro to Add Row to the Bottom of a Table

Click OK. And you’ll get a new empty row added to the bottom of the table.

Excel Macro to Add Empty Row to the Bottom of a Table


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

You can also add a new row filled with data to the bottom of the table.

Let’s insert a row filled with the data Angela Hopkins, $35,000, and $40,000 to the bottom of the table Table1.

The VBA code will be the following:

VBA Code:

Sub Add_Row_with_Data()

Dim ws As Worksheet
Set ws = ActiveSheet

Table_Name = InputBox("Enter the Name of the Table: ")

Dim tbl As ListObject
Set tbl = ws.ListObjects(Table_Name)

Dim NewRow As ListRow
Set NewRow = tbl.ListRows.Add

Dim Data() As String
Data = Split(InputBox("Enter the Data to Fill: "), ",")

For i = 0 To UBound(Data)
    NewRow.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.

Running Macro to Develop Excel Macro to Add Row to the Bottom of a Table

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

Entering Input to Develop Excel Macro to Add Row to the Bottom of a Table

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).

Click OK. And you’ll have your data entered inside a new row to the bottom of your table.


Things to Remember

Here we’ve used the ListObject and the ListRow, two very important objects of VBA. To know more about VBA objects and collections, visit this link.


Conclusion

Using these methods, you can develop Excel Macro to add a row to the bottom of a table. Do you have any questions? Feel free to ask us.


Related Readings

Rifat Hassan

Rifat Hassan

Hello! Welcome to my profile. Here I will be posting articles related to Microsoft Excel. I am a passionate Electrical Engineer holding a Bachelor’s degree in Electrical and Electronic Engineering from Bangladesh University of Engineering and Technology. Besides academic studies, I always love to keep pace with the revolution in technology that the world is rushing towards day by day. I am diligent, career-oriented, and ready to cherish knowledge throughout my life.

3 Comments
  1. Hi Rifat,

    Thanks for your explanation! Very usefull. Just one question, what if I want to skip the table name input because I have only one table which is always the same?

    • Hi Dennis,

      Thanks for your response. To skip the table name, first, remove the 4th line of the code, that is, Table_Name = InputBox(“Enter the Name of the Table: “).

      Then enter the name of the table within the 6th line of the code. For example, if the name of your table is “Table1”, then the line should be: Set tbl = ws.ListObjects(“Table1”).

      So the complete code will be:

      Sub Add_Empty_Row()

      Dim ws As Worksheet
      Set ws = ActiveSheet

      Dim tbl As ListObject
      Set tbl = ws.ListObjects(“Table1”)

      tbl.ListRows.Add

      End Sub

  2. how to add data into bottom of table with data copied from another table.

Leave a reply

ExcelDemy
Logo