CountIf for unfiltered cells.
 

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

CountIf for unfiltered cells.
 

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

CountIf for unfiltered cells.
 

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

CountIf for unfiltered cells.
 

for each cell in Range("A2:A100")
if cell.EntireRow.Hidden = True then


End if
Next
--
Regards,
Tom Ogilvy



"woodinville dave" wrote in
message ...
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

CountIf for unfiltered cells.
 

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

CountIf for unfiltered cells.
 

Thanks a bunch! that solved my problem. My final function is:
Function CountIfVisible(UserRange, criteria)
Dim count As Integer
count = 0
For Each cell In UserRange
If cell.EntireRow.Hidden = False Then
If cell.Value = criteria Then
count = count + 1
End If
End If
Next cell
CountIfVisible = count
End Function

--
- Dave


"Tom Ogilvy" wrote:

for each cell in Range("A2:A100")
if cell.EntireRow.Hidden = True then


End if
Next
--
Regards,
Tom Ogilvy



"woodinville dave" wrote in
message ...
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

CountIf for unfiltered cells.
 

Ignore this reply.

Dave Peterson wrote:

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

--

Dave Peterson