|
COUNTCONTIGROWS2 Function
The COUNTCONTIGROWS2 function counts the number of rows from a reference row down to the last cell before the next visible empty cell in the referenced columns.
COUNTCONTIGROWS2 Syntax
COUNTCONTIGROWS2 (theColumns)
TheColumns
A range reference to a cell or cells in the columns whose rows are to be counted.
COUNTCONTIGROWS2 counts the number of rows downwards from the referenced cell to the cell before the first visible empty cell found.
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.
COUNTCONTIGROWS2 Remarks
COUNTCONTIGROWS2 is 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 non-empty row before the first empty cell in each column will be found, and the largest rowcount will be returned.
COUNTCONTIGROWS2 uses the same criteria as Ctrl-Down Arrow to detect empty cells, except when the reference cell or the cell below the reference cell is empty.
COUNTCONTIGROWS2 will return 1 if the cell specified by theColumns is empty and the next cell down is also empty.
If the cell specified by theColumns is empty but the cells below it are not empty then COUNTCONTIGROWS2 will look downwards from the cell below the cell specified by 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 intermediate empty row(s) in the range are not visible COUNTCONTIGROWS2 will NOT treat them as empty.
|
COUNTCONTIGROWS2 is not recommended for ranges with hidden rows or rows with zero heights.
|
Because hiding rows/columns or setting width/heights to zero does not trigger a recalculation COUNTCONTIGROWS2 may show an incorrect value even in automatic mode until the workbook is recalculated. Changing the Automatic or Advanced filter does trigger a recalculation.
COUNTCONTIGROWS2 is not recommended for ranges containing hidden rows or rows with zero heights.
|
COUNTCONTIGROWS2 Example
Assuming that cells B4:B55 contain 10 and cell B2:B3 and cell B56 are empty then:
=COUNTCONTIGROWS2(B4) returns 52 (51 consecutive non-empty rows from B5 to B55 plus row 4 itself).
=COUNTCONTIGROWS2(B2) returns 1 (the cell below B2 is empty)
=COUNTCONTIGROWS2(B3) returns 53 (52 consecutive non-empty rows from B4 to B55 plus row 3 itself even though B3 is empty).
|
