VBA Add row to Table in Excel

VBA Add row to Table in Excel. We can add a single row or multiple rows and data to table. Default new rows added at the end of the table. In this tutorial we have explained multiple examples with explanation. We also shown example output screenshots. We have specified three examples in the following tutorial. You can change table and sheet name as per your requirement. We also specified step by step instructions how to run VBA macro code at the end of the session.

Syntax to Add Row to Table using VBA in Excel

Here is the syntax to add new row to table on the worksheet using VBA in Excel.

expression.Add(Position, AlwaysInsert)

Where expression represents the ListRows.
Position is an optional parameter. It represents the relative position of the new row. Accepts the Integer value.
AlwaysInsert is an optional parameter. It represents the cells to be shifted to down or not, based on Boolean value. Accepts the Boolean value either True or False.

Note: If position is not specified, default adds new row at the end of the table.

Example to Add New Row to Table on the Worksheet

Let us see the example to add new row to table on the worksheet. The sheet name defined as ‘Table‘. And we use table name as ‘MyDynamicTable‘. You can change these two as per your requirement. We Add method of the ListObject object.

 'VBA Add New Row to Table
Sub VBAF1_Add_Row_to_Table()
    
    'Declare Variables
    Dim oSheetName As Worksheet
    Dim sTableName As String
    Dim loTable As ListObject
    
    'Define Variable
    sTableName = "MyDynamicTable"
    
    'Define WorkSheet object
    Set oSheetName = Sheets("Table")
    
    'Define Table Object
    Set loTable = oSheetName.ListObjects(sTableName)
    
    'Add New row to the table
    loTable.ListRows.Add
       
End Sub

Output: Here is the following output screenshot of above example macro VBA code.

Add Multiple Rows to Table in Excel using VBA

Here is another example to add multiple rows to table. In this example we add five(5) rows to the table. You can specify the number of rows count in the for loop.

'VBA Add Multiple Rows to Table
Sub VBAF1_Add_Multiple_Rows_to_Table()
    
    'Declare Variables
    Dim oSheetName As Worksheet
    Dim sTableName As String
    Dim loTable As ListObject
    Dim iCnt As Integer
    
    'Define Variable
    sTableName = "MyDynamicTable"
    
    'Define WorkSheet object
    Set oSheetName = Sheets("Table")
    
    'Define Table Object
    Set loTable = oSheetName.ListObjects(sTableName)
    
    For iCnt = 1 To 5 'You can change based on your requirement
        'Add multiple rows to the table
        loTable.ListRows.Add
    Next
    
End Sub

Output: Let us see the following output screenshot of above example macro VBA code.

Add Row & Data to Table on the Worksheet in Excel

Let us see how to add new row and data to the table using VBA in Excel. In the below example we add new row and data of 5 columns.

 'VBA Add Row and Data to Table
Sub VBAF1_Add_Row_And_Data_to_Table()
    
    'Declare Variables
    Dim oSheetName As Worksheet
    Dim sTableName As String
    Dim loTable As ListObject
    Dim lrRow As ListRow
    
    'Define Variable
    sTableName = "MyDynamicTable"
    
    'Define WorkSheet object
    Set oSheetName = Sheets("Table")
    
    'Define Table Object
    Set loTable = oSheetName.ListObjects(sTableName)
    
    'Add New row to the table
    Set lrRow = loTable.ListRows.Add
    
    'Add Data to recently added row
    With lrRow
        .Range(1) = 20
        .Range(2) = 30
        .Range(3) = 40
        .Range(4) = 50
        .Range(5) = 60
    End With
       
End Sub

Output: Here is the following output screenshot of above example VBA macro code.

Instructions to Run VBA Macro Code or Procedure:

You can refer the following link for the step by step instructions.

Instructions to run VBA Macro Code

Other Useful Resources:

Click on the following links of the useful resources. These helps to learn and gain more knowledge.

VBA Tutorial VBA Functions List VBA Arrays in Excel VBA Tables and ListObjects

VBA Editor Keyboard Shortcut Keys List VBA Interview Questions & Answers Blog