Tom answered your question, but you really aren't using the equivalent of
=countif(). You're just using =subtotal(3, ...). The 3 means that you want to
use CountA as your subtotal function.
woodinville dave wrote:
>
> Yeah subtotal works for count but not countif. I wish I could just pass in a
> function to subtotal to do countif.
>
> Is there a way to test the individual cell to see if it is visible?
> --
> - Dave
>
> "Dave Peterson" wrote:
>
> > If you're trying to use this UDF from a worksheet formula, then you're seeing
> > the trouble that excel has with .specialcells.
> >
> > I think you're going to have to loop through the range and test to see if it's
> > visible.
> >
> > ===
> > BTW, if you're hiding rows via data|filter|autofilter, you could use the
> > worksheet formula:
> >
> > =subtotal(3,a1:a10)
> >
> > If you're using xl2003, then you could even use:
> > =subtotal(103,a1:a10)
> >
> > =subtotal() was enhanced in xl2003 to ignore manually hidden rows.
> >
> >
> >
> > woodinville dave wrote:
> > >
> > > I'm trying to implement a countif for visible cells. When I use the filter it
> > > doesn't seem to affect the values I get back for xlCellTypeVisible. This is
> > > my function, I'm sure I am doing something stupid.
> > >
> > > Function CountIfVisible(UserRange, criteria)
> > > Dim count As Integer
> > > count = 0
> > > For Each cell In UserRange.SpecialCells(xlCellTypeVisible)
> > > If cell.Value = criteria Then
> > > count = count + 1
> > > End If
> > > Next cell
> > > CountIfVisible = count
> > > MsgBox count
> > > End Function
> > >
> > > Thanks for any help,
> > > Dave
> >
> > --
> >
> > Dave Peterson
> >
--
Dave Peterson
|
Similar Threads
