You can use the following syntax to count the number of rows in a table in Excel using VBA:

Sub CountTableRow()

Dim tbl As ListObject

'specify table to count rows in
Set tbl = ActiveSheet.ListObjects("Table1")

'create message box that displays row count
MsgBox "Total Rows: " & tbl.Range.Rows.Count & vbNewLine & _
       "Header Rows: " & tbl.HeaderRowRange.Rows.Count & vbNewLine & _
       "Body Rows: " & tbl.ListRows.Count

'set tbl variable to Nothing
Set tbl = Nothing

End Sub

This particular example counts the following number of rows for the table in the active sheet called Table1:

• Total number of rows
• Total number of header rows
• Total number of body rows

We use the MsgBox function to create a message box that displays each of these values in one message box.

Note: We use the vbNewLine statement to insert new lines and create a message box with multiple lines.

The following example shows how to use this syntax in practice.

Example: How to Count Rows in Table Using VBA

Suppose we have the following table called Table1 that contains information about various basketball players:

We can create the following macro to count the number of rows in this table:

Sub CountTableRow()

Dim tbl As ListObject

'specify table to count rows in
Set tbl = ActiveSheet.ListObjects("Table1")

'create message box that displays row count
MsgBox "Total Rows: " & tbl.Range.Rows.Count & vbNewLine & _
       "Header Rows: " & tbl.HeaderRowRange.Rows.Count & vbNewLine & _
       "Body Rows: " & tbl.ListRows.Count

'set tbl variable to Nothing
Set tbl = Nothing

End Sub

When we run this macro, we receive the following output:

The message box tells us that there are 10 total rows in the table.

It also tells us that there is 1 header row and 9 body rows.