My Autofilter is overzealous!
 

Hi guys,

I have a macro that does a filter on a named range in another open workbook
and copies the results to the active cell. This works fine except when the
search and filter comes up with nothing (no hits) then the entire named
range is copied!. I would have thought because there is an IF in there it
would do nothing with no hits. I'm sure this is simple but I cant get it!
A couple of other questions if I may. Is there an easier way of getting the
weekday from a serial than WeekdayName(Weekday(R1C4))? It seems a bit long
winded.
What the easiest way to focus on the first empty cell in a column?
Hope you can help

Thanks
Mark

Sub Test4()
'
' Test4 Macro
' Macro recorded 31/10/2003 by Mark
' Keyboard Shortcut: Ctrl+i
'
Let sheetDate = Range("I1").Value 'gets date value
if Selection.AutoFilter Field:=7, Criteria1:="BARSAC" then
Range("ProgramData").Select
Range("ProgramData").Activate
Selection.Copy
Windows("BARSAC.xls").Activate

ActiveCell.Range("D1").Value = sheetDate 'paste date
ActiveCell.Offset(1, 0).Select
ActiveSheet.Paste
ActiveCell.Offset(-1, 3).Font.Bold = True
With ActiveCell.Offset(-1, 0)
.FormulaR1C1 = WeekdayName(Weekday(R1C4))
.Font.Bold = True
End With

End Sub

My Autofilter is overzealous!
 

You can check the count of matching rows with this function:

Function RowMatchCount(WS As Worksheet) As Long
Dim MatchCount As Long, Area As Range
For Each Area In
WS.Range("_FilterDatabase").SpecialCells(xlCellTyp eVisible).Areas
MatchCount = MatchCount + Area.Rows.Count
Next
RowMatchCount = MatchCount - 1
End Function

Sub Demo()
MsgBox RowMatchCount(Worksheets("Sheet1"))
End Sub


--
Jim Rech
Excel MVP