Table of Contents
To count the rows in a table using VBA, you can use the Range.Rows.Count function. This will return the number of rows in the table. An example of this is to create a macro that uses the Range.Rows.Count function to count the number of rows in a table, which can then be used to loop through each row and perform some task.
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.