Counting visible cells in a range

The COUNTVISIBLE function, which follows, accepts a range argument and returns the number of non-empty visible cells in the range. A cell is not visible if it's in a hidden row or a hidden column.

Function COUNTVISIBLE(rng) ' Counts visible cells Dim CellCount As Long Dim cell As Range Application.Volatile CellCount = 0

Set rng = IntersectCrng.Parent.UsedRange, rng) For Each cell In rng

If Not IsEmpty(cell) Then

If Not cell.EntireRow.Hidden And _ Not cell.EntireColumn.Hidden Then _ CellCount = CellCount + l End If

Next cell

COUNTVISIBLE = CellCount End Function

This function loops through each cell in the range, first checking whether the cell is empty. If it's not empty, it checks the hidden properties of the cell's row and column. If either the row or column is hidden, the CellCount variable is incremented.

The COUNTVISIBLE function is useful when you're working with AutoFilters or outlines. Both of these features make use of hidden rows.

Excel's SUBTOTAL function (with a first argument of 2 or 3) is also useful for counting visible cells in an AutoFiltered list.

In Excel 2003,you can add 100 to the first argument of the SUBTOTAL function. Doing so causes the function to use only visible cells in its calculation. For example, to count the visible numeric cells in a range, use a first argument of 102.This new feature works even if rows or columns are hidden manually. In previous versions, the SUBTOTAL function worked correctly only if the cells were hidden by using AutoFilters or outlines.

Was this article helpful?

0 0

Post a comment