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.

Create a Table in Excel

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.

Add a New Row in an Excel Table

Step 2:

  • Now, the VBA window will appear. Choose Module from the Insert tab.

Add a New Row in an Excel Table

  • 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

Add a New Row in an Excel Table

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.


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

VBA Code to Add a Row with Data in an Excel Table

Step 2:

  • Hit F5 to run the VBA code.

We can see that a new row with data has been included here.


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

Overwrite Previous Data in an Existing Row in Excel Table

Step 2:

  • 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.

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

Delete a Row in a Table with Excel VBA

Step 2:

  • 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.

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

Show Values of Each Cell in a Column in Excel Table

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.

Show Values of Each Cell in a Column in Excel Table

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.

Tags:

Alok

Alok

Hello, this is Alok. I am working as an Excel & VBA Content Developer at Exceldemy. I want to provide solutions to various Excel-based problems. I completed my study at East West University major in Telecommunications Engineering. I love traveling, reading books, playing cricket.

We will be happy to hear your thoughts

Leave a reply

ExcelDemy
Logo