Excel Macro to Add Row to the Bottom of a Table

Last updated: Nov 14, 2023

Get FREE Advanced Excel Exercises with Solutions!

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.

Table of Contents hide

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

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

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

Practice Section

Things to Remember

Conclusion

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.

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.

Download Practice Workbook

Macro to Add Row to the Bottom of a Table.xlsm

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.

Sub SelectedDatatoBottom() Dim ExistingTable As Object Dim mTable As Range On Error Resume Next Set mTable = Application.InputBox( _ Title:="Copy Data to Paste to Bottom", _ Prompt:="Select Cells to Copy", _ Type:=8) On Error GoTo 0 Set ExistingTable = Worksheets("vba").ListObjects("Table1").ListRows.Add mTable.Copy ExistingTable.Range.PasteSpecial xlPasteValues Application.CutCopyMode = False Worksheets("vba").Activate End Sub

Sub SelectedDatatoBottom() Dim x As Worksheet Dim ExistingTable As ListObject Dim mTable As ListObject Set x = ActiveSheet On Error Resume Next Set mTable = x.ListObjects("Pipe_Live") On Error GoTo 0 Set ExistingTable = Worksheets("RollingPivot").ListObjects("LongTable") mTable.DataBodyRange.Copy ExistingTable.ListRows.Add ExistingTable.ListRows(ExistingTable.ListRows.Count).Range.PasteSpecial xlPasteValues Application.CutCopyMode = False Worksheets("RollingPivot_test").Activate End Sub

Sub SelectedDatatoBottom() Dim x As Worksheet Dim ExistingTable As ListObject Dim mTable As ListObject Dim i As Long Set x = ActiveSheet On Error Resume Next Set mTable = x.ListObjects("Pipe_Live") On Error GoTo 0 Set ExistingTable = Worksheets("RollingPivot").ListObjects("LongTable") For i = 1 To mTable.DataBodyRange.Rows.Count ExistingTable.ListRows.Add ExistingTable.ListRows(ExistingTable.ListRows.Count).Range.Value = mTable.DataBodyRange.Rows(i).Value Next i Application.CutCopyMode = False Worksheets("RollingPivot_test").Activate End Sub

Sub SelectedDatatoBottom() Dim x As Worksheet Dim ExistingTable As ListObject Dim mTable As ListObject Set x = ActiveSheet Set mTable = x.ListObjects("Pipe_Live") On Error Resume Next Set ExistingTable = Worksheets("RollingPivot").ListObjects("LongTable") On Error GoTo 0 If ExistingTable Is Nothing Then MsgBox "The target table 'LongTable' was not found in the 'RollingPivot' worksheet.", vbCritical Exit Sub End If If ExistingTable.ListRows.Count = 0 Then ExistingTable.ListRows.Add End If Dim targetRange As Range If ExistingTable.ListRows.Count = 0 Then Set targetRange = ExistingTable.DataBodyRange.Rows(1) Else Set targetRange = ExistingTable.ListRows(ExistingTable.ListRows.Count).Range.Offset(1) End If mTable.DataBodyRange.Resize(mTable.DataBodyRange.Rows.Count).Copy targetRange.PasteSpecial xlPasteValues Application.CutCopyMode = False Worksheets("RollingPivot_test").Activate End Sub

10 Comments

Dennis Feb 15, 2022 at 3:10 PM

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?
- Reply
  Rifat Hassan Feb 19, 2022 at 1:52 AM
  
  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
mani Jul 23, 2022 at 12:06 AM

how to add data into bottom of table with data copied from another table.
- Reply
  Maruf Islam Sep 4, 2022 at 2:46 PM
  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.
```
Sub SelectedDatatoBottom()
Dim ExistingTable As Object
Dim mTable As Range
On Error Resume Next
  Set mTable = Application.InputBox( _
      Title:="Copy Data to Paste to Bottom", _
      Prompt:="Select Cells to Copy", _
      Type:=8)
  On Error GoTo 0
Set ExistingTable = Worksheets("vba").ListObjects("Table1").ListRows.Add
mTable.Copy
ExistingTable.Range.PasteSpecial xlPasteValues
Application.CutCopyMode = False
Worksheets("vba").Activate
End Sub
```
  Download the Excel file for your better assistance.
  Regards
  Maruf Islam (Exceldemy Team)
salih Feb 7, 2023 at 10:04 PM

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)
- Reply
  Rubayed Razib Suprov Feb 16, 2023 at 3:34 PM
  
  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”)
SALIH Feb 27, 2023 at 12:35 AM

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.
- Reply
  Shahriar Abrar Rafid Feb 28, 2023 at 9:50 AM
  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:
```
Sub SelectedDatatoBottom()
Dim x As Worksheet
Dim ExistingTable As ListObject
Dim mTable As ListObject
Set x = ActiveSheet
On Error Resume Next
Set mTable = x.ListObjects("Pipe_Live")
On Error GoTo 0
Set ExistingTable = Worksheets("RollingPivot").ListObjects("LongTable")
mTable.DataBodyRange.Copy
ExistingTable.ListRows.Add
ExistingTable.ListRows(ExistingTable.ListRows.Count).Range.PasteSpecial xlPasteValues
Application.CutCopyMode = False
Worksheets("RollingPivot_test").Activate
End Sub
```
  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:
```
Sub SelectedDatatoBottom()
Dim x As Worksheet
Dim ExistingTable As ListObject
Dim mTable As ListObject
Dim i As Long
Set x = ActiveSheet
On Error Resume Next
Set mTable = x.ListObjects("Pipe_Live")
On Error GoTo 0
Set ExistingTable = Worksheets("RollingPivot").ListObjects("LongTable")
For i = 1 To mTable.DataBodyRange.Rows.Count
ExistingTable.ListRows.Add
ExistingTable.ListRows(ExistingTable.ListRows.Count).Range.Value = mTable.DataBodyRange.Rows(i).Value
Next i
Application.CutCopyMode = False
Worksheets("RollingPivot_test").Activate
End Sub
```
  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.
  - Reply
    SALIH Feb 28, 2023 at 2:37 PM
    
    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?
    - Shahriar Abrar Rafid Mar 1, 2023 at 4:17 PM
      
      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.
      
      Sub SelectedDatatoBottom() Dim x As Worksheet Dim ExistingTable As ListObject Dim mTable As ListObject Set x = ActiveSheet Set mTable = x.ListObjects("Pipe_Live") On Error Resume Next Set ExistingTable = Worksheets("RollingPivot").ListObjects("LongTable") On Error GoTo 0 If ExistingTable Is Nothing Then MsgBox "The target table 'LongTable' was not found in the 'RollingPivot' worksheet.", vbCritical Exit Sub End If If ExistingTable.ListRows.Count = 0 Then ExistingTable.ListRows.Add End If Dim targetRange As Range If ExistingTable.ListRows.Count = 0 Then Set targetRange = ExistingTable.DataBodyRange.Rows(1) Else Set targetRange = ExistingTable.ListRows(ExistingTable.ListRows.Count).Range.Offset(1) End If mTable.DataBodyRange.Resize(mTable.DataBodyRange.Rows.Count).Copy targetRange.PasteSpecial xlPasteValues Application.CutCopyMode = False Worksheets("RollingPivot_test").Activate End Sub
      
      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

Excel Macro to Add Row to the Bottom of a Table

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

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

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

Practice Section

Things to Remember

Conclusion

Related Articles

What is ExcelDemy?

Leave a reply Cancel reply