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

Sub CountRows()
    Range("E2") = Range("A:A").Cells.SpecialCells(xlCellTypeConstants).Count
End Sub

This particular example counts the number of rows in column A and then assigns the result to cell E2.

If you would instead like to display the row count in a message box, you can use the following syntax:

Sub CountRows()
    'Create variable to store number of rows
    Dim row_count As Integer
    
    'Calculate number of rows in range
    row_count = Range("A:A").Cells.SpecialCells(xlCellTypeConstants).Count
    
    'Display the result
    MsgBox "Rows in Column A: " & row_countEnd Sub

The following examples shows how to use each of these methods in practice with the following dataset in Excel that contains the names of basketball players on three different teams:

Example 1: Count Rows in Range Using VBA and Display Results in Cell

Suppose we would like to calculate the number of rows in column A and output the results in a specific cell.

We can create the following macro to do so:

Sub CountRows()
    Range("E2") = Range("A:A").Cells.SpecialCells(xlCellTypeConstants).Count
End Sub

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

Notice that cell E2 contains a value of 9.

This tells us that the there are 9 rows in column A with values.

Example 2: Count Rows in Range Using VBA and Display Results in Message Box

Suppose we would instead like to count the number of rows in column A and output the results in a message box.

Sub CountRows()
    'Create variable to store number of rows
    Dim row_count As Integer
    
    'Calculate number of rows in range
    row_count = Range("A:A").Cells.SpecialCells(xlCellTypeConstants).Count
    
    'Display the result
    MsgBox "Rows in Column A: " & row_countEnd Sub

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

The message box tells us that there are 9 rows in column A with values.

Note that in this example we calculated the number of rows in all of column A using A:A as our range.

However, if you’d like to instead calculate the number of rows with values in a specific range, you could type A2:A9 (or whatever range you’re interested in) instead.