Count number of rows in a range

This tutorial shows how to count the total number of rows from a selected range through the use of an Excel formula or VBA

Example: Count number of rows in a range

Count number of rows in a range

METHOD 1. Count number of rows in a range

EXCEL

=ROWS(E5:E15)
This formula uses the Excel ROWS function to return the number of rows in the selected range. In this example we have selected range (E5:E15) inside the ROWS function, which returns a total of 11 rows.

METHOD 1. Count number of rows in a range using VBA

VBA

Sub Count_number_of_rows_in_range()
'declare a variable
Dim ws As Worksheet
Set ws = Worksheets("Analysis")
'count the total number of rows in a specific range
ws.Range("B5") = ws.Range("E5:E15").Rows.Count

End Sub

ADJUSTABLE PARAMETERS
Output Range: Select the output range by changing the cell reference ("B5") in the VBA code.
Range: Select the range from which you want to count the number of rows by changing the range reference ("E5:E15") in the VBA code.
Worksheet Selection: Select the worksheet in which you want to count the number of rows from a selected range by changing the Analysis worksheet name in the VBA code. You can also change the name of this object variable, by changing the name 'ws' in the VBA code.

Explanation about the formula used to count number of rows in a range

EXPLANATION

EXPLANATION

This tutorial shows how to count the total number of rows from a selected range through the use of an Excel formula or VBA.
Both the Excel formula and VBA approach make use of the ROWS function to count the number of rows in a selected range.
Using the VBA method you will also need to combine the Rows function with the Count function to return the total number of rows in a selected range.
FORMULA
=ROWS(array)
ARGUMENTS
array: An array or reference to a range of cells.

RELATED TOPICS

Related Topic Description Related Topic and Description
How to count the total number of columns in a range using Excel and VBA methods
How to count the total number of cells in a range using Excel and VBA methods
How to count the total number of characters, including spaces, in a cell using Excel and VBA methods
How to count the total number of characters in a cell, excluding spaces, using Excel and VBA methods
How to ccount the total number of characters in a range, including spaces, using Excel and VBA methods

RELATED FUNCTIONS

Related Functions Description Related Functions and Description
The Excel ROWS function returns the number of rows in a specified array