Excel VBA Code for Each Row in a Table (Add, Overwrite, Delete, etc.)

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.

Download Practice Workbook

Download this practice workbook to exercise while you are reading this article.

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.

5 VBA Examples to Add, Overwrite, or Delete Each Row in a Table in Excel

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: How to Add New Row Automatically in an Excel 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: How to Create a Table in Excel with Data (5 Ways)

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: Types of Tables in Excel: A Complete Overview

Similar Readings

• How to Make Group by Same Interval in Excel Pivot Table (2 Methods)
• Calculated Field Sum Divided by Count in Pivot Table
• Excel Pivot Table Group by Week (3 Suitable Examples)
• [Fix] Cannot Group Dates in Pivot Table: 4 Possible Solutions
• How to Calculate Percent Frequency Distribution in Excel (2 Methods)

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 Insert or Delete Rows and Columns from Excel Table

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.

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.com and give your suggestions in the comment box.

Related Articles

• Navigating Excel Table: Selecting Parts of a Table and Moving a Table
• Excel Table Formatting Tips – Change the Look of the Table
• Pivot Table Field Name Is Not Valid: 9 Causes and Corrections
• What is the Difference between a Table and a Range in Excel?
• How to Make Excel Tables Look Good (8 Effective Tips)
• How to Refresh All Pivot Tables with VBA (4 Ways)