MrExcel Publishing
Your One Stop for Excel Tips & Solutions

Counting Hidden Cells

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

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 <> 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



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



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


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


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.


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?