Home |
Search |
Today's Posts |
#1
|
|||
|
|||
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. |
#2
|
|||
|
|||
After autofiler, use
ret = Application.WorksheetFunction.Subtotal(3, Range("A:A")) MsgBox (ret) |
#3
|
|||
|
|||
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 |
#4
|
|||
|
|||
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) |
#5
|
|||
|
|||
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 |
#6
|
|||
|
|||
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 |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
Find duplicate rows then deleting them | Excel Worksheet Functions | |||
how can i filter from two rows? | Excel Discussion (Misc queries) | |||
increasing the number of rows of a worksheet beyond 65536? | Excel Discussion (Misc queries) | |||
Can I set a filter for a merged column across multiple rows and o. | Excel Worksheet Functions | |||
Data Filter - Not all rows in spreadsheet will display in Autofilt | Excel Worksheet Functions |