| Home |
| Search |
| Today's Posts |
|
#1
Posted to microsoft.public.excel.programming
|
|||
|
|||
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 |
|
#2
Posted to microsoft.public.excel.programming
|
|||
|
|||
|
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 |
|
#3
Posted to microsoft.public.excel.programming
|
|||
|
|||
|
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 |
|
#4
Posted to microsoft.public.excel.programming
|
|||
|
|||
|
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 |
|
#5
Posted to microsoft.public.excel.programming
|
|||
|
|||
|
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 |
|
#6
Posted to microsoft.public.excel.programming
|
|||
|
|||
|
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 |
|
#7
Posted to microsoft.public.excel.programming
|
|||
|
|||
|
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 |
| Reply |
| Thread Tools | Search this Thread |
| Display Modes | |
|
|
Similar Threads
|
||||
| Thread | Forum | |||
| paste filtered data in unfiltered list | Excel Discussion (Misc queries) | |||
| filtering in a worksheet and then cut items out i lose unfiltered | Excel Discussion (Misc queries) | |||
| Problem viewing back the "Unfiltered range" | Excel Discussion (Misc queries) | |||
| How do I delete only the unfiltered row? | Excel Discussion (Misc queries) | |||
| Excel 2007 Autofilter deleting unfiltered Data | Excel Worksheet Functions | |||
Linear Mode
