[Excel VBA] How to Add New Records to a Table | Using ListObject.ListRows.Add

Excel Tables (ListObject) are very convenient as structured data. Understanding how to add records (rows) using VBA is incredibly helpful for automating data entry tasks.

In this article, I will explain how to add the “next record” to a table using VBA, along with specific code examples.

Expected Use Cases

• Adding new information from a data entry form to a table.
• Registering data obtained from external files or user input into an existing table structure.
• Always appending new data to the end of a table.

Basic Syntax: Adding a New Record to a Table

The following code adds one row to the bottom of the table named “MemberData” (originally “会員データ”).

Sub AddNewRecord()

    Dim tbl As ListObject
    Set tbl = ActiveSheet.ListObjects("MemberData")

    ' Add the next record (to the end)
    tbl.ListRows.Add

End Sub

Advanced: Inserting a Row at a Specific Position

If you want to insert a new record at the 2nd row, specify the position like this:

tbl.ListRows.Add 2

• Note: Since the row is inserted before the existing row, be aware that the row numbers following it will shift.

Adding a Record with Data

It is also possible to set specific data for the added row immediately

Sub AddRecordWithData()

    Dim tbl As ListObject
    Dim newRow As ListRow

    Set tbl = ActiveSheet.ListObjects("MemberData")

    ' Add a record and assign it to a variable
    Set newRow = tbl.ListRows.Add

    ' Set values using an Array (Pay attention to column order)
    newRow.Range.Value = Array("M1001", "Taro Mori", #1/1/2020#)

End Sub

Important Points

Summary

Using VBA makes adding records to Excel tables very easy to implement.

By utilizing the ListRows.Add method, you can dynamically register data without manual user operation. This leads to improved business efficiency and form automation.

This method is effective for automating routine tasks and preventing input errors, so please try incorporating it into your projects.