|
COUNTCONTIGCOLS2 Function
The COUNTCONTIGCOLS2 function counts the number of COLUMNS from a reference row down to the last cell before the next visible empty cell in the referenced columns.
COUNTCONTIGCOLS2 Syntax
COUNTCONTIGCOLS2 (theRows)
TheRows
A range reference to a cell or cells in the rows whose columns are to be counted.
COUNTCONTIGCOLS2 counts the number of columns across to the right from the referenced cell to the cell before the first visible empty cell found.
The count includes the first column of the referenced cell.
If theRows refers to more than one adjacent row then the count gives the largest column count found for the rows.
COUNTCONTIGCOLS2 Remarks
COUNTCONTIGCOLS2 is a volatile function.
If the reference specified for theRows contains more than one column, the left-most column will be used.
If the reference specified for theRows contains more than one row, the last non-empty column before the first empty cell in each row will be found, and the largest column count will be returned.
COUNTCONTIGCOLS2 uses the same criteria as Ctrl-Right Arrow to detect empty cells, except when the reference cell or the cell to the right of the reference cell is empty.
COUNTCONTIGCOLS2 will return 1 if the cell specified by theRows is empty and the next cell to the right is also empty.
If the cell specified by theRows is empty but the cells to the right of it are not empty then COUNTCONTIGCOLS2 will look to the right from the cell adjacent to the cell specified by theRows.
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 column(s) in the range are not visible COUNTCONTIGCOLS2 will NOT treat them as empty.
|
COUNTCONTIGCOLS2 is not recommended for ranges with hidden columns or columns with zero heights.
|
Because hiding rows/columns or setting width/heights to zero does not trigger a recalculation COUNTCONTIGCOLS2 may show an incorrect value even in automatic mode until the workbook is recalculated. Changing the Automatic or Advanced filter does trigger a recalculation.
COUNTCONTIGCOLS2 is not recommended for ranges containing hidden columns or columns with zero heights.
|
COUNTCONTIGCOLS2 Example
Assuming that cells C4:G4 contain 10 and cell A4, B4 and H4 are empty then:
=COUNTCONTIGCOLS2(C4) returns 5 (4 consecutive non-empty COLUMNS from D4 to G4 plus column C itself).
=COUNTCONTIGCOLS2(A4) returns 1 (the cell to the right of A4 is empty)
=COUNTCONTIGCOLS2(B4) returns 6 (5 consecutive non-empty COLUMNS from C4 to G4 plus column B itself even though B4 is empty).
|
