Counting Hidden Cells

Posted by David on June 01, 2000 6:01 PM

Greetings,
I'm trying to count visible cells in a named cell range so that I can filter data and have a number which represents the number of unfiltered rows. I can get the function (see below) to work in a sub routine but not in an excel formula (I get the #value message). Can someone please tell me where I'm going wrong:

Function DCountVisible(NamedRange As String) As Integer
Set RowsToCount = Range(NamedRange).CurrentRegion.Rows
For Each Row In RowsToCount
If Row.Hidden &LT;&GT; True Then
D2CountVisible = D2CountVisible + 1
End If
Next Row
End Function

Posted by Ivan Moala on June 02, 0100 1:06 AM

Re: Need More Help

Put in your formula @ start

Application.Volatile

Ivan

Posted by Celia on June 01, 0100 7:50 PM

David

Are you using qoutes for your NamedRange? Like this :-

=DCountVisible("NamedRange")

( Also, in the line of code that has D2CountVisible, shouldn't this be DCountVisible? )

Celia

Posted by David on June 01, 0100 8:09 PM

Thanks Celia,

The extra "2" was the result of an experiment carelessly left in when I pasted the formula into the message. The brackets were the problem. It is working now.

Cheers

Posted by David on June 01, 0100 10:17 PM

Need More Help

OK it all working fine except that when using the formula in the spreadsheet it does not behave like a normal formula as the formula does not update(recalculate) automatically unless the "=" is pressed again. Is there anything that I can do to make it behave like a normal formula?

Thanks