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_count
End 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.

We can create the following macro to do so:

**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_count
End 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.

**Additional Resources**

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

VBA: How to Write AVERAGEIF and AVERAGEIFS Functions

VBA: How to Write SUMIF and SUMIFS Functions

VBA: How to Write COUNTIF and COUNTIFS Functions