|
COUNTROWS2 Function
The COUNTROWS2 function counts the number of rows from a reference row to the last visible non-empty cell in the referenced columns.
COUNTROWS2 Syntax
COUNTROWS2(theColumns)
TheColumns
A range reference to a cell or cells in the columns whose rows are to be counted.
COUNTROWS2 counts the number of rows, including blanks and empty cells down to the last visible non-empty cell.
The count includes the top row of the referenced cell.
If theColumns refers to more than one adjacent column then the count gives the largest row count found for the columns.
COUNTROWS 2Remarks
COUNTROWS 2is a volatile function.
If the reference specified for theColumns contains more than one row, the top row will be used.
If the reference specified for theColumns contains more than one column, the last visible non-empty row in each column will be found, and the largest row count will be returned.
COUNTROWS2uses the same criteria as Ctrl-Up Arrow to detect empty cells. The last row found is equivalent to selecting the last row in the worksheet and pressing Ctrl-Up-Arrow, except that it will stop at the cell specified by theColumns.
COUNTROWS2 will return 1 if the cell specified by theColumns is empty and there are no other visible non-empty cells below theColumns
A cell that contains nothing but is formatted is treated as an empty cell.
A cell that contains ‘ will look empty but will be treated as a non-empty cell.
Invisible cells can be created using Automatic or Advanced Filter, hiding rows or columns, or setting row heights or column widths to zero.
If the last row(s) in the range are not visible then COUNTROWS2 will not count them.
If intermediate row(s) in the range are not visible COUNTROWS2 will count them.
|
Countrows2should be used with care on ranges with hidden rows or rows with zero heights.
|
Because hiding rows/columns or setting width/heights to zero does not trigger a recalculation COUNTROWS2 may show an incorrect value even in automatic mode until the workbook is recalculated. Changing the Automatic or Advanced filter does trigger a recalculation.
|
COUNTROWS2 Example
Assuming that cell B55 contains 10 and there are no other non-empty cells in column B then:
=COUNTROWS2(B4) returns 52 (51 rows from B4 to B55 plus row 4 itself).
|
