|
COUNTCOLS2 Function
The COUNTCOLS2 function counts the number of columns before the last visible non-empty cell in the referenced row.
COUNTCOLS2 Syntax
COUNTCOLS2(theRows)
TheRows
A range reference to a cell or cells in the rows whose columns are to be counted.
COUNTCOLS2 counts the number of columns, including blanks and empty cells across to the right to the last visible non-empty cell.
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.
COUNTCOLS2 Remarks
COUNTCOLS2 is a volatile function.
If the reference specified for theRows contains more than one column, the first column will be used.
If the reference specified for theRows contains more than one row, the last non-empty column in each row will be found, and the largest column count will be returned.
COUNTCOLS2 uses the same criteria as Ctrl-Left Arrow to detect empty cells. The last column found is equivalent to selecting the last column in the worksheet and pressing Ctrl-Left, except that it will stop at the cell specified by theRows.
COUNTCOLS2 will return 1 if the cell specified by theRows is empty and there are no other non-empty cells to the right of 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 the last column(s) in the range are not visible COUNTCOLS2 will not count them.
If intermediate column(s) in the range are not visible COUNTCOLS2 will count them.
|
COUNTCOLS2 should 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 COUNTCOLS2 may show an incorrect value even in automatic mode until the workbook is recalculated. Changing the Automatic or Advanced filter does trigger a recalculation.
|
COUNTCOLS2 Example
Assuming that cell D42 contains FRED and there are no other non-empty cells in row 42 then:
=COUNTCOLS2(B42) returns 3 (2 columns from B42 to D42 plus the B column itself).
|
