Count Total Rows in a Table in Excel

Powerful spreadsheet software like Microsoft Excel enables you to carry out a variety of data analysis and manipulation operations. Knowing how to count the rows in a table is a useful ability that can save you time and effort whether you're a student, professional, or just someone trying to organise and analyse data efficiently.

Excel table's function is a fantastic tool for managing and organising your data. Knowing the total number of rows in a table will help you rapidly estimate the size of your data set, make wise judgements, and easily do calculations or apply formulas. We'll look at a variety of approaches to complete this work in this tutorial, from basic strategies to more sophisticated ones. In order to find the total number of rows in an Excel table, let's review the numerous approaches.

Count Total Rows in a Table

Here we will first insert a VBA module and then run it to complete the task. So let us see a simple process to know how you can count total rows in a table in Excel.

Step 1

Consider an Excel sheet where you have a table similar to the below image.

First, right-click on the sheet name and select View Code to open the VBA application.

Right click > View Code.

Step 2

Then click on Insert and select Module, then copy the below code into the text box.

Insert > Module > Copy.

Code

Sub CountTableRows()
   Dim xTable As ListObject
   Dim xTName As String
   On Error Resume Next
   xTName = Application.InputBox("Please input the table name?", "Count Total Rows", , , , , , 2)
   Set xTable = ActiveSheet.ListObjects(xTName)
   MsgBox "The table has total number of " & xTable.Range.Rows.Count & " rows.", vbInformation, "Count Total Rows"
   Set xTable = Nothing
End Sub

Step 3

Then click F5 to run the module. Then enter the table name and click OK.

F5 > Table Name > Ok.

Then you can see that a number of rows will be displayed.

Conclusion

In this tutorial, we have used a simple example to demonstrate how you can count the total rows in a table in Excel to highlight a particular set of data.