Count with Hidden cells

S

Stretch

Is there a way to count the number of text filled cells excluding the cells
that I have hidden?
 
P

Peo Sjoblom

Depends on the version of excel, excel 2003 works like

=SUBTOTAL(103,A1:A10)

won't count hidden cells

--
Regards,

Peo Sjoblom

(No private emails please)
 
S

Stretch

What does the 103 stand for?

Peo Sjoblom said:
Depends on the version of excel, excel 2003 works like

=SUBTOTAL(103,A1:A10)

won't count hidden cells

--
Regards,

Peo Sjoblom

(No private emails please)
Click to expand...
 
D

Dave Peterson

The 3 in 103 represents the =CountA() function.

Look at xl's help for all 11 of these codes.

xl2003 adds 100 to each of those 11 to tell =subtotal() to ignore both the rows
hidden by filters and rows hidden manually.
 
D

Dave Peterson

I think you'll need a userdefined function.

Option Explicit
Function myCount(rng As Range)
Application.Volatile True

Dim myCell As Range
Dim myCtr As Long

myCtr = 0
For Each myCell In rng.Cells
If IsEmpty(myCell) _
Or myCell.EntireRow.Hidden = True _
Or myCell.EntireColumn.Hidden = True Then
'do nothing, ignore it
Else
myCtr = myCtr + 1
End If
Next myCell

myCount = myCtr

End Function

======
In xl2002, I don't think that hiding rows causes a recalculation, though. You
may want to hit F9 before you trust the result of the UDF. (xl2003 recalcs when
you hide a row--but you don't need this kind of function in xl2003--as Peo
said.)

If you're new to macros, you may want to read David McRitchie's intro at:
http://www.mvps.org/dmcritchie/excel/getstarted.htm

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:
=mycount(a2:a33)
 
Top