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.
- Overview on VBA Add Row to Table in Excel
- Syntax to Add Row to Table using VBA in Excel
- Example to Add New Row to Table on the Worksheet in Excel
- Add Multiple Rows to Table in Excel using VBA
- Add Row & Data to Table on the Worksheet in Excel
- Instructions to use VBA Macro code
- Other Related References
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 Use Example Macro Code
You can follow step by step instructions to run above macro example VBA code.
- Open Visual Basic Editor(VBE) by clicking Alt +F11
- Go to code window by clicking F7
- Copy above specified macro or procedure
- Paste above copied code in code window in the visual basic editor(VBE)
- Run macro by clicking F5 or Run command
- The difference between before and after macro, see the output screenshot.
Other Related References
Click on the following links for related reference articles. It helps for your reference.
Hi,
I spent ages looking for exactly this, but I’ve copied everything as planned and edited the areas to match the details on my workbook but nothing happens. It takes a few moments after running the macro but no data appears in the table.
The data I am trying to add is referenced from a different worksheet, could that be the problem?