Excel ROWS Function
The Excel ROWS function returns the number of rows in a specified array
Example: Excel ROWS Function
|
=ROWS(B2)
|
Result in cell B5 (1) - returns the number of rows in the selected reference.
|
|
=ROWS(E3:F11)
|
Result in cell B6 (9) - returns the number of rows in the selected range.
|
|
=ROWS(A:A)
|
Result in cell B7 (1048576) - returns the number of rows in an entire column.
|
METHOD 2. Excel ROWS function using the Excel built-in function library
EXCEL
Formulas tab > Function Library group > Lookup & Reference > ROWS > populate the input box
| =ROWS(B2) Note: in this example we are populating the Array input box with a single range reference. |
 |
Sub Excel_Rows_Function()
'declare a variable
Dim ws As Worksheet
Dim ws As Worksheet
Set ws = Worksheets("ROWS")
'apply the Excel ROWS function
ws.Range("B5") = ws.Range("B2").Rows.Count
ws.Range("B6") = ws.Range("E3:F11").Rows.Count
ws.Range("B7") = ws.Range("A:A").Rows.Count
ws.Range("B5") = ws.Range("B2").Rows.Count
ws.Range("B6") = ws.Range("E3:F11").Rows.Count
ws.Range("B7") = ws.Range("A:A").Rows.Count
End Sub
OBJECTS
Worksheets: The Worksheets object represents all of the worksheets in a workbook, excluding chart sheets.
Range: The Range object is a representation of a single cell or a range of cells in a worksheet.
Worksheets: The Worksheets object represents all of the worksheets in a workbook, excluding chart sheets.
Range: The Range object is a representation of a single cell or a range of cells in a worksheet.
PREREQUISITES
Worksheet Name: Have a worksheet named ROWS.
Worksheet Name: Have a worksheet named ROWS.
ADJUSTABLE PARAMETERS
Output Range: Select the output range by changing the Range references ("B5"), ("B6") and ("B7") in the VBA code to any cell in the worksheet, that doesn't conflict with the formula.