How to Add a Row to the Bottom of a Table Using an Excel Macro (2 Methods)

Dataset Overview

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.

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

Open the Microsoft Visual Basic window by pressing Alt+F11.
Go to the Insert tab and click on Module.

Enter the following VBA code to create a macro called Add_Empty_Row:

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

Code Breakdown:

- 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.
- It adds a new ListRow to the bottom of the table.

Save the file as an Excel Macro-Enabled Workbook.
Run the Macro.

An input box will prompt you to enter the table name (e.g., Table1).

Click OK to add an empty row to the bottom of the table.

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

Scenario: Insert a row filled with the data Angela Hopkins, $35,000, and $40,000 to the bottom of Table1.

Open a Module in the Microsoft Visual Basic window.
Enter the following VBA code to create a macro called Add_Row_with_Data:

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

Code Breakdown:

This code creates a Macro called Add_Row_with_Data.
It takes the name of a table as input from the user and then sets the table as a ListObject.
It then adds a new ListRow to the bottom of the table.
It takes the data as input and then adds it to the empty row.

Save the file as an Excel Macro-Enabled Workbook.

Run the Macro. Two input boxes will appear:
- The first asks for the table name (e.g., Table1).
- The second asks for data to fill the new row (e.g., Angela Hopkins,35000,40000 without spaces after the commas).

Click OK, and the data will be added to a new row at the bottom of your table.

Practice Section

We have provided a practice section on each sheet on the right side.

Things to Remember

That we’ve used the ListObject and ListRow objects in VBA for these methods.

Download Practice Workbook

You can download the practice workbook from here:

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

Get FREE Advanced Excel Exercises with Solutions!

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