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