Counting only visible rows and not using a filter

[Carl Olsson]

Hi,

I want one cell in my spreadsheet to always show the number of
non-empty visible cells in a range. Subtotal(3, rng) only seems to
work with autofilter and I'm hiding my rows programmatically. Any
ideas somebody?

/Carl

 

[Brad]

You can create a standard module and include this function:

Function NonEmptyVisible(Cell_Range As Range) As Integer
Dim Cell As Range
Dim intCounter As Integer
For Each Cell In Cell_Range
If Cell.EntireRow.Hidden = False Then
If Not IsEmpty(Cell.Value) Then
intCounter = intCounter + 1
End If
End If
Next
NonEmptyVisible = intCounter
End Function

Then just reference it like a regular worksheet function -
passing the range you want to use. It's not very
efficient though, and you'll need to find another option
if we're talking about large amounts of rows.

-Brad

 

[Peo Sjoblom]

I believe subtotal in excel 2003 will work with hidden rows as well