Rifat Hassan
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.
Download Practice Workbook
2 Ways to Develop Excel Macro to Add Row to the Bottom of a Table
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.
Code Breakdown:
- This code creates a Macro called Add_Empty_Row.
- Moreover, 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.
⧭ 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.
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
Code Breakdown:
- This code creates a Macro called Add_Row_with_Data.
- Then, it takes the name of a table as input from the user and then sets the table as a ListObject.
- After that, it adds a new ListRow to the bottom of the table.
- Finally, it takes the data as input and then adds it to the empty row.
⧭ 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.
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.
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.
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
how to add data into bottom of table with data copied from another table.
Greetings Mani,
Use the following macro code to copy data (from tables or normal ranges) to the bottom of an existing table (i.e., Table1 or anything).
The macro fetches an input box to select the preferred range to copy and paste afterward. Change the Worksheet Name (i.e., vba) and Table Name (i.e., Table1) as existing table.
Download the Excel file for your better assistance.
https://www.exceldemy.com/wp-content/uploads/2022/09/Macro_to_Copy_Data_to_Bottom.xlsm
Regards
Maruf Islam (Exceldemy Team)
Great Article.
Facing an error though.
Runtime Error: 9
Subscript out of Range.
The line at Table_Name is where the error is at. I tried replacing the input with directly the table name (like below), but still same error.
Set t = x.ListObjects(Depart_Filter5)
Thanks a lot Salih, for your query
I think I understood your problem. Here, your cant put the table name in this way. You have to insert the table name in between two apostrophe symbol. So basically your code line should be like
Set t = x.ListObjects(“Depart_Filter5”)
Guys Thanks for you valuable inputs. I’m trying to copy all rows of a table to the bottom of another table. Below is my code but it errors at line “mTable.Copy” : ‘Object doesn’t support this property or method’
“`
Sub SelectedDatatoBottom()
Dim x As Worksheet
Dim ExistingTable As Object
Dim mTable As Object
Set x = ActiveSheet
On Error Resume Next
Set mTable = x.ListObjects(“Pipe_Live”)
On Error GoTo 0
Set ExistingTable = Worksheets(“RollingPivot”).ListObjects(“LongTable”).ListRows.Add
mTable.Copy
ExistingTable.Range.PasteSpecial xlPasteValues
Application.CutCopyMode = False
Worksheets(“RollingPivot_test”).Activate
End Sub
I would highly appreciate your help.
Hello SALIH,
The “Copy” method is not permitted for the object “mTable,” according to the error message “Object doesn’t support this property or method.” Instead, you may use the script to copy the data from the mTable to the bottom of the ExistingTable:
Rather than replicating the full mTable in this code, “mTable.DataBodyRange.Copy” merely copies the table’s data range. The copied data is then put into the newly inserted row using the “ExistingTable.ListRows.Add” and “ExistingTable.ListRows(ExistingTable.ListRows.Count).Range.PasteSpecial xlPasteValues” commands.
Also, with VBA, there are different approaches to copying every row of a table to the bottom of another table. Another option is to copy every entry from the data source to the bottom of the
target table using a loop:
This code uses the “For i = 1 To mTable.DataBodyRange.Rows.Count” statement to iterate through each row in the source table’s data body range. “ExistingTable.ListRows.Add” is used to add a new row to the bottom of the destination table for each row, and “ExistingTable.ListRows(ExistingTable.ListRows.Count).Range.Value = mTable.DataBodyRange.Rows(i).Value” is used to copy the values from the current row of the source table to the new row of the destination table.
Thanks very much!
I get the error *PasteSpecial method of class Range failed.* for the below line.*
“`
ExistingTable.ListRows(ExistingTable.ListRows.Count).Range.PasteSpecial xlPasteValues
“`
The second code works well. But it takes a bit of time. If the first method works fine will it be faster? If so, would you know how to fix that error?
Hello again SALIH,
How are you? I know it feels so irritating when your script doesn’t run properly. So, I’ve come up with a bit of modification to the previous one. Hope it’ll not disappoint you.
Thanks for your query. Feel free to ask if you need any assistance regarding Excel or Office-related applications. Happy Excelling…
Regards,
Shahriar Abrar Rafid
Team ExcelDemy