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, we need to know how to make a Table in Excel. There are lots of ways to create a table in Excel. We are showing the easiest way in this section.
Step 1:
- 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.
Step 2:
- 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.
Step 1:
- 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.
Step 2:
- 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.
Step 3:
- 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
Step 4:
- 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.
Step 1:
- 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
Step 2:
- 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.
Step 1:
- 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
Step 2:
- Run the VBA code by pressing F5.
We successfully overwrote the data of Row 3 from table Exceldemy_5.
Read More:Â How to Create a Table with Headers Using Excel VBA
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.
Step 1:
- 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
Step 2:
- Hit the F5 button to run the code.
Here, we can see that 2nd row of the table has been deleted.
Read More:Â How to Compare Two Tables for Differences with Excel VBA
5. Show Values of Each Cell in a Column in Excel Table
By this example, we can view each row of a certain column.
Step 1:
- 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
Step 2:
- Run the VBA code by pressing F5.
The first row of the Age column is shown in the result.
Step 3:
- Now, click OK repeatedly to view the rest of the results.
Results are shown sequentially.
Download Practice Workbook
Conclusion
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.
