How to Count Rows in VBA

In this article, we will introduce how to count rows in VBA with examples.

Count Rows in VBA

When we are working with multiple excel sheets with a large number of data and want to count how many rows are in an excel sheet, we need to use a fundamental function for this task.

There are two methods to use the function: we can define the range and count the rows using the Rows method with a sub-method of Count. Now we learn the rules of checking rows count.

Firstly we identify the limit of data from which we extract the Rows count. The second step is to add a dot(.) to get the appearance of the list which contain properties.

Next is to select the row property, and the final step is to use the count property. Let’s have an example where we will create demo data and use Range to count the rows from the demo data.

The demo data is shown below.

Code Example:

Sub CntRow()
cnt = Range("A1:A12").Rows.Count
MsgBox (cnt)
End Sub

Output:

The above example displays that the range contains 12 rows. Let’s go through another example in which we will use the UsedRange instead of just a Range.

UsedRange is used when we want to get the data from the range used. This method excludes any empty row.

In the following example, we’ll use the same demo data, except we deleted the last 3 rows.

Code:

#VBA
Sub CntRow()
   MsgBox Worksheets("Sheet1").UsedRange.Rows.Count
End Sub

Output: