[Excel VBA] How to Get the Record and Column Counts of a Table | Using ListRows.Count and ListColumns.Count

Using structured tables (ListObject) in Excel makes data management more efficient.

By using VBA, you can easily retrieve the number of records (rows) and columns (items) in a table.

This article introduces how to get table size information using ListRows.Count and ListColumns.Count.

Expected Use Cases

• Checking the record count whenever data increases or decreases.
• Dynamically changing the processing target according to the number of columns.
• Monitoring and verifying the table structure (e.g., for template quality control).

Basic Syntax: Getting Record and Column Counts

Sub GetTableRecordInfo()

    Dim tbl As ListObject
    Set tbl = ActiveCell.ListObject ' Get the table containing the active cell

    ' Output results to the sheet
    Range("E2").Value = "Record Count"
    Range("F2").Value = tbl.ListRows.Count

    Range("E3").Value = "Column Count"
    Range("F3").Value = tbl.ListColumns.Count

End Sub

What is .ListObject?

.ListObject is a property used to retrieve the table (ListObject) to which a specified cell belongs.

If the active cell is inside a table, you can automatically retrieve the entire table.

Meaning of .ListRows.Count and .ListColumns.Count

Advanced: Getting Information for All Tables at Once

Sub GetAllTableInfo()

    Dim tbl As ListObject
    Dim i As Integer: i = 2

    For Each tbl In ActiveSheet.ListObjects
        Cells(i, 1).Value = tbl.Name
        Cells(i, 2).Value = tbl.ListRows.Count
        Cells(i, 3).Value = tbl.ListColumns.Count
        i = i + 1
    Next

End Sub

Summary

By retrieving the number of records and columns in a table using VBA, you can programmatically grasp the data structure and use it for dynamic processing.

This function is very useful for report processing and database-style management.

Especially in environments where users frequently add or delete data, this technique is essential for improving processing accuracy and automation stability.