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



" 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