Find the number of rows returned in a filter

I would like to filter a list and test for no match. For example

Sub FilterTest(market as string)

Excel.Application.Screenupdating = false
Excel.sheets("SS").visible = true
Excel.sheets("SS").select
Excel.sheets("SS").range("B10").select
Excel.Selection.AutoFilter
Excel.Selection.AutoFilter Field:=1, Criteria1:="<>1*", Operator:=xlAnd, _
Criteria2:="<>*T"
Excel.Application.screenupdating = true
'now here is what I want to test
If (the filter retuns no visible records or rows) then
Msgbox "The "& market & " market does not meet this criteria.",vbOKOnly
end if

End sub

I am not sure of the proper syntax to count the number of visible
records(rows) in an Excel filter. Basically if the count is 0 then display
the message box.

Thanks for your help in advance!
Note: I use Excel. for each line because people open my excel files in
Internet Explorer (they use Xerox DocuShare or SharePoint). If you know a
better way to handle ScreenUpdating,DisplayAlerts and commandbars that would
be great. Initially I would receive an error from IE when trying to turn
on/off screenupdating and display alers.

After autofiler, use

ret = Application.WorksheetFunction.Subtotal(3, Range("A:A"))
MsgBox (ret)

Maybe this will give you some ideas:

Option Explicit
Sub testme()

Dim rngF As Range

With ActiveSheet.AutoFilter.Range
Set rngF = .Columns(1).Cells.SpecialCells(xlCellTypeVisible)
If rngF.Cells.Count = 1 Then
'only the header is visible
MsgBox "no details shown"
Else
MsgBox rngF.Cells.Count - 1 & " rows of data visible"
End If
End With
End Sub

And I try not to open any excel workbook within MSIE. If you want that:

How to Configure Internet Explorer to Open Office
Documents in the Appropriate Office Program Instead of in Internet Explorer
http://support.microsoft.com/?scid=162059



Tony W wrote:
>
> I would like to filter a list and test for no match. For example
>
> Sub FilterTest(market as string)
>
> Excel.Application.Screenupdating = false
> Excel.sheets("SS").visible = true
> Excel.sheets("SS").select
> Excel.sheets("SS").range("B10").select
> Excel.Selection.AutoFilter
> Excel.Selection.AutoFilter Field:=1, Criteria1:="<>1*", Operator:=xlAnd, _
> Criteria2:="<>*T"
> Excel.Application.screenupdating = true
> 'now here is what I want to test
> If (the filter retuns no visible records or rows) then
> Msgbox "The "& market & " market does not meet this criteria.",vbOKOnly
> end if
>
> End sub
>
> I am not sure of the proper syntax to count the number of visible
> records(rows) in an Excel filter. Basically if the count is 0 then display
> the message box.
>
> Thanks for your help in advance!
> Note: I use Excel. for each line because people open my excel files in
> Internet Explorer (they use Xerox DocuShare or SharePoint). If you know a
> better way to handle ScreenUpdating,DisplayAlerts and commandbars that would
> be great. Initially I would receive an error from IE when trying to turn
> on/off screenupdating and display alers.

--

Dave Peterson

Thanks! I'll have to read up on the Subtotal arguments. The solution works
great!
--
Anthony White



"babs00@hotmail.com" wrote:

> After autofiler, use
>
> ret = Application.WorksheetFunction.Subtotal(3, Range("A:A"))
> MsgBox (ret)
>
>

Thanks Dave but the solution will only solve that issue on my computer.
Hundreds of people view my data via DocuShare and SharePoint and I can't
control the settings on their computer. I also have to write code testing
their version of Excel( mainly 97 vs 2000 and beyond) since I load my
dropdown boxes with pivot tables and clear missing items before loading.
Thanks for the link!
--
Anthony White



"Dave Peterson" wrote:

> Maybe this will give you some ideas:
>
> Option Explicit
> Sub testme()
>
> Dim rngF As Range
>
> With ActiveSheet.AutoFilter.Range
> Set rngF = .Columns(1).Cells.SpecialCells(xlCellTypeVisible)
> If rngF.Cells.Count = 1 Then
> 'only the header is visible
> MsgBox "no details shown"
> Else
> MsgBox rngF.Cells.Count - 1 & " rows of data visible"
> End If
> End With
> End Sub
>
> And I try not to open any excel workbook within MSIE. If you want that:
>
> How to Configure Internet Explorer to Open Office
> Documents in the Appropriate Office Program Instead of in Internet Explorer
> http://support.microsoft.com/?scid=162059
>
>
>
> Tony W wrote:
> >
> > I would like to filter a list and test for no match. For example
> >
> > Sub FilterTest(market as string)
> >
> > Excel.Application.Screenupdating = false
> > Excel.sheets("SS").visible = true
> > Excel.sheets("SS").select
> > Excel.sheets("SS").range("B10").select
> > Excel.Selection.AutoFilter
> > Excel.Selection.AutoFilter Field:=1, Criteria1:="<>1*", Operator:=xlAnd, _
> > Criteria2:="<>*T"
> > Excel.Application.screenupdating = true
> > 'now here is what I want to test
> > If (the filter retuns no visible records or rows) then
> > Msgbox "The "& market & " market does not meet this criteria.",vbOKOnly
> > end if
> >
> > End sub
> >
> > I am not sure of the proper syntax to count the number of visible
> > records(rows) in an Excel filter. Basically if the count is 0 then display
> > the message box.
> >
> > Thanks for your help in advance!
> > Note: I use Excel. for each line because people open my excel files in
> > Internet Explorer (they use Xerox DocuShare or SharePoint). If you know a
> > better way to handle ScreenUpdating,DisplayAlerts and commandbars that would
> > be great. Initially I would receive an error from IE when trying to turn
> > on/off screenupdating and display alers.
>
> --
>
> Dave Peterson
>

Babs00 solution was also macro based. And if you have empty cells in that
range, you may find =subtotal(3,...) doesn't count all the visible cells--it
counts all the visible cells that have something in them.



Tony White wrote:
>
> Thanks Dave but the solution will only solve that issue on my computer.
> Hundreds of people view my data via DocuShare and SharePoint and I can't
> control the settings on their computer. I also have to write code testing
> their version of Excel( mainly 97 vs 2000 and beyond) since I load my
> dropdown boxes with pivot tables and clear missing items before loading.
> Thanks for the link!
> --
> Anthony White
>
> "Dave Peterson" wrote:
>
> > Maybe this will give you some ideas:
> >
> > Option Explicit
> > Sub testme()
> >
> > Dim rngF As Range
> >
> > With ActiveSheet.AutoFilter.Range
> > Set rngF = .Columns(1).Cells.SpecialCells(xlCellTypeVisible)
> > If rngF.Cells.Count = 1 Then
> > 'only the header is visible
> > MsgBox "no details shown"
> > Else
> > MsgBox rngF.Cells.Count - 1 & " rows of data visible"
> > End If
> > End With
> > End Sub
> >
> > And I try not to open any excel workbook within MSIE. If you want that:
> >
> > How to Configure Internet Explorer to Open Office
> > Documents in the Appropriate Office Program Instead of in Internet Explorer
> > http://support.microsoft.com/?scid=162059
> >
> >
> >
> > Tony W wrote:
> > >
> > > I would like to filter a list and test for no match. For example
> > >
> > > Sub FilterTest(market as string)
> > >
> > > Excel.Application.Screenupdating = false
> > > Excel.sheets("SS").visible = true
> > > Excel.sheets("SS").select
> > > Excel.sheets("SS").range("B10").select
> > > Excel.Selection.AutoFilter
> > > Excel.Selection.AutoFilter Field:=1, Criteria1:="<>1*", Operator:=xlAnd, _
> > > Criteria2:="<>*T"
> > > Excel.Application.screenupdating = true
> > > 'now here is what I want to test
> > > If (the filter retuns no visible records or rows) then
> > > Msgbox "The "& market & " market does not meet this criteria.",vbOKOnly
> > > end if
> > >
> > > End sub
> > >
> > > I am not sure of the proper syntax to count the number of visible
> > > records(rows) in an Excel filter. Basically if the count is 0 then display
> > > the message box.
> > >
> > > Thanks for your help in advance!
> > > Note: I use Excel. for each line because people open my excel files in
> > > Internet Explorer (they use Xerox DocuShare or SharePoint). If you know a
> > > better way to handle ScreenUpdating,DisplayAlerts and commandbars that would
> > > be great. Initially I would receive an error from IE when trying to turn
> > > on/off screenupdating and display alers.
> >
> > --
> >
> > Dave Peterson
> >

--

Dave Peterson