How to Count Rows in Table Using VBA (With Example)


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.

Additional Resources

The following tutorials explain how to perform other common tasks in VBA:

How to Count Unique Values in Range Using VBA
How to Count Number of Rows in Range Using VBA
How to Count Cells with Specific Text Using VBA

Featured Posts

Leave a Reply

Your email address will not be published. Required fields are marked *