Excel Table is an interesting feature of Microsoft Excel. A table is a group of cells with data for a particular use. In Excel, we can use data from a certain table by calling that table only. In this article, we will discuss the different operations of each row in a table with Excel VBA.
How to Create a Table in Excel
- First, select all the cells which will be included in the table.
- Now, press Ctrl+T.
- Create Table dialog box will appear.
- Our selected range will show there.
- Mark on My table has headers option.
- Finally, press OK.
The name of the table will show on the name bar. We can modify the name from here.
Excel VBA Code for Each Row in a Table (Add, Overwrite, Delete, etc.): 5 VBA Examples
We will show 5 VBA examples that explain the different operations of each row of the Excel table.
1. Add a New Row in an Excel Table with VBA Code
We will show how to add new rows to an Excel table in this example.
- First, go to the Sheet Name section that will be found at the bottom border of each sheet. Press the right button of the mouse.
- Choose the View Code option from the list.
- Now, the VBA window will appear. Choose Module from the Insert tab.
- The VBA command module will come finally. We will write the VBA code on this window.
- Copy and paste the following VBA code into this window.
Sub VBA_table_1() Dim t_name As ListObject Set t_name = ActiveSheet.ListObjects("Exceldemy_2") t_name.ListRows.Add t_name.ListRows.Add 4 End Sub
- Finally, run the code by pressing the F5 button.
We can see that two rows are added to the dataset. t_name.ListRows.Add, this command adds a new row at the bottom of the table and t_name.ListRows.Add 4, command adds a new row at the 4th row.
Read More: Excel VBA: Insert Data into Table
2. Add a Row with Data in an Excel Table
We will add new rows with data in this example. Data and rows will insert at the same time in the table.
- Enter the VBA command module by pressing the Alt+F11 keys.
- Put the following VBA code on the command module.
Sub VBA_table_2() Dim t_name As ListObject Set t_name = ActiveSheet.ListObjects("Exceldemy_3") Dim new_row As ListRow Set new_row = t_name.ListRows.Add With new_row .Range(1) = "Tony" .Range(2) = 29 .Range(3) = 1220 End With End Sub
- Hit F5 to run the VBA code.
We can see that a new row with data has been included here.
Read More: Excel VBA to Create Table from Range
3. Overwrite Previous Data in an Existing Row in Excel Table
This example will show how to overwrite data of an existing row of a table with Excel VBA.
- Enter the VBA command module by pressing Alt+F11.
- Put the following VBA code here.
Sub VBA_table_3() Dim t_name As ListObject Set t_name = ActiveSheet.ListObjects("Exceldemy_5") With t_name.ListRows(3) .Range(1) = "Tony" .Range(2) = 29 .Range(3) = 1220 End With End Sub
- Run the VBA code by pressing F5.
We successfully overwrote the data of Row 3 from table Exceldemy_5.
4. Delete a Row in a Table with Excel VBA Macro
From the previous examples, we learned how to add rows to a table. Now, we will see how to delete rows from the Excel table.
- Hit Alt+F11 keys to enter the VBA command module.
- Copy the VBA code below and paste it onto the VBA command module.
Sub VBA_table_4() Dim t_name As ListObject Set t_name = ActiveSheet.ListObjects("Exceldemy_6") t_name.ListRows(2).Delete End Sub
- Hit the F5 button to run the code.
Here, we can see that 2nd row of the table has been deleted.
5. Show Values of Each Cell in a Column in Excel Table
By this example, we can view each row of a certain column.
- Press Alt+F11 and enter the VBA command module.
- Write the following VBA code here.
Sub VBA_table_5() Dim table_1 As ListObject Dim row_1 As ListRow Set table_1 = ActiveSheet.ListObjects("Exceldemy_7") For Each row_1 In table_1.ListRows MsgBox ("Age :" & Intersect(row_1.Range, table_1.ListColumns("Age").Range).Value) Next row_1 End Sub
- Run the VBA code by pressing F5.
The first row of the Age column is shown in the result.
- Now, click OK repeatedly to view the rest of the results.
Results are shown sequentially.
Download Practice Workbook
In this article, we described how to evaluate each row in a table with Excel VBA. We also showed how to add the column to a table. I hope this will satisfy your needs. Please have a look at our website Exceldemy and give your suggestions in the comment box.