Thanks Gary and Dave. I don't use macros a lot but I'm not new to them. I'll
implement something like along the lines you suggest, nice idea.

Thanks,
Alain


"Dave Peterson" wrote in message
...
You can use a user defined function.

Option Explicit
Function CountVisibleNonBlank(rng As Range) As Long

Application.Volatile

Dim HowMany As Long
Dim myCell As Range

HowMany = 0

For Each myCell In rng.Cells
If IsEmpty(myCell.Value) Then
'skip it
Else
If myCell.EntireRow.Hidden = True _
Or myCell.EntireColumn.Hidden = True Then
'skip it
Else
HowMany = HowMany + 1
End If
End If
Next myCell

CountVisibleNonBlank = HowMany

End Function

Be aware that if you change the rowheight (hide/show more rows in the
range),
then xl97 won't recalc this formula (I think this feature was added in
xl2003????). Don't trust the results until you force a recalc.

If you're new to macros:

Debra Dalgleish has some notes how to implement macros he
http://www.contextures.com/xlvba01.html

David McRitchie has an intro to macros:
http://www.mvps.org/dmcritchie/excel/getstarted.htm

Ron de Bruin's intro to macros:
http://www.rondebruin.nl/code.htm

(General, Regular and Standard modules all describe the same thing.)

Short course:

Open your workbook.
Hit alt-f11 to get to the VBE (where macros/UDF's live)
hit ctrl-R to view the project explorer
Find your workbook.
should look like: VBAProject (yourfilename.xls)

right click on the project name
Insert, then Module
You should see the code window pop up on the right hand side

Paste the code in there.

Now go back to excel.
Into a test cell and type:
=CountVisibleNonBlank(B1:B6)

Alain Dekker wrote:

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

--

Dave Peterson