Counting visible cells in a range

The CountVisible function that follows accepts a range argument and returns the number of nonempty 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 = Intersect(rng.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 + 1 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 neither the row nor 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.

If you're using Excel 2003 or later, the CountVisible function is no longer required. You can use Excel's SUBTOTAL function instead. For example, to count the visible numeric cells in a range named data, use this formula:

=SUBTOTAL(103,data)

This formula 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.

0 0

Responses

  • rowan brown
    How to count visible cells vba?
    7 years ago

Post a comment