Count of visible, non-blank cells only?

[Alain Dekker]

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

 

[Pete_UK]

Use this instead:

=SUBTOTAL(102,B1:B6)

Excel Help on SUBTOTAL explains the meaning of the 102 (and other
numbers that can be used).

Hope this helps.

Pete

 

[Stefi]

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

„Alain Dekker†ezt írta:

 

[Alain Dekker]

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

 

[Pete_UK]

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

 

[Alain Dekker]

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

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

 

[Dave Peterson]

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 here:
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)

 

[Gary''s Student]

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/WritingFunctionsInVBA.aspx

 

[Alain Dekker]

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