[Excel VBA] How to Manipulate Table Data by Record: Using ListRow

When working with Excel Tables (ListObject) in VBA, there are often times when you want to retrieve, edit, or delete data one row (record) at a time.

In VBA, you can use ListRows(row_number) to easily manipulate specific records.

This article explains how to individually retrieve, select, and delete table rows (ListRow).

Typical Use Cases

• You want to select and check the data in the 2nd row only.
• You want to delete only rows that match specific conditions.
• You want to loop through the table row by row.

VBA Code Examples

Selecting a Specific Record

Sub SelectTableRecord()

    Dim tbl As ListObject
    Dim myRow As ListRow

    ' Set the table (Change "CustomerList" to your actual table name)
    Set tbl = ActiveSheet.ListObjects("CustomerList")
    
    ' Specify the 2nd row (data part)
    Set myRow = tbl.ListRows(2) 

    ' Select the corresponding record
    myRow.Range.Select

End Sub

Deleting a Specific Record

Sub DeleteTableRecord()

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

    ' Delete the record in the 2nd row
    tbl.ListRows(2).Delete

End Sub

What is ListRow?

ListRow is an object that represents “one record (one row)” of a table.

By using this object, you can flexibly control the table, such as selecting, editing, deleting, or retrieving values from any specific row.

Important Notes

Advanced Example: Looping Through All Records

Dim tbl As ListObject
Dim r As ListRow

Set tbl = ActiveSheet.ListObjects("CustomerList")

For Each r In tbl.ListRows
    ' Check if the value in the 2nd column of the row is "Mori"
    If r.Range.Cells(1, 2).Value = "Mori" Then
        MsgBox "Mori's data found."
    End If
Next

Summary

In Excel VBA, you can process data flexibly record by record using table rows (ListRow).

Whether you need to process only specific rows or manipulate rows based on conditions, this method meets practical business needs. Please use this technique to improve efficiency and prevent input errors.