Consider filtered data in B2 thru B100. Some of the visible values may be
blank. The following UDF:

Public Function VisiCount(r As Range) As Long
Dim rr As Range
VisiCount = 0
For Each rr In r
If rr.EntireRow.Hidden = False Then
If rr.Value < "" Then
VisiCount = VisiCount + 1
End If
End If
Next
End Function

will give you the desired count.


UDFs are very easy to install and use:

1. ALT-F11 brings up the VBE window
2. ALT-I
ALT-M opens a fresh module
3. paste the stuff in and close the VBE window

If you save the workbook, the UDF will be saved with it.

To use the UDF from the normal Excel window, just enter it like a normal
Excel Function. For example =VisiCount(B2:B100)

To remove the UDF:

1. bring up the VBE window as above
2. clear the code out
3. close the VBE window

To learn more about UDFs, see:

http://www.cpearson.com/excel/Writin...ionsInVBA.aspx
--
Gary''s Student - gsnu200904


"Alain Dekker" wrote:

Thanks Pete. Is this not possible in Excel 97 then? Perhaps there's a
slightly more complex function I could use to achieve the same thing?

Regards,
Alain

"Pete_UK" wrote in message
...
Originally SUBTOTAL had those numbers and only ignored values which
were hidden by means of Autofilter. In later versions (I think 2002
onwards) they introduced other numbers with 100 added onto the ones
you quote, and these cause the function to ignore not only filtered-
out values but also values that were hidden manually (as you
describe).

Pete

On Sep 23, 11:47 am, "Alain Dekker"
wrote:
Thanks for those swift replies.

Unfortunately, I left out an important bit of information: I'm using Excel
97 SR-2. When I checked the help on "SUBTOTAL" there was no reference to
the
value "102". The help only mentions these values:

1 AVERAGE
2 COUNT
3 COUNTA
4 MAX
5 MIN
6 PRODUCT
7 STDEV
8 STDEVP
9 SUM
10 VAR
11 VARP

If I try any number higher than "11" for the ref_num parameter, I just get
an error. Is this "102" function something from a later version of Excel
and
if so, can I do this in Excel 97?

Thanks again,
Alain

"Stefi" wrote in message

...



=SUBTOTAL(102,A1:A6)
Regards,
Stefi

"Alain Dekker" ezt Ã*rta:

Hi,

I set up a new workbook and put the values 1,2,3,4 into cells A1 to A4
respectively. Leave A5 and A6 blank. Now I put the formula
"=count(B1:B6)"
into cell A7 and the values is 4 (ie. 4 non-blank cells).

All well and good.

Now I hide row 3. The value still stays 4. I know this is correct, but
is
there another formula that just counts visible, non-blank cells?

Thanks,
Alain- Hide quoted text -

- Show quoted text -