Filter Criteria Test

[Geoff Taylor]

I have code that passes criteria to a column filter.

" ActiveSheet.Range("$A$66:$G$13900").AutoFilter Field:=7, Criteria1:=Range("$B$8").Value"

What I would like to do is test the filter to ensure that there is actually some data that matches the criteria. I know I could loop down the entire range and test each cell but that would take additional time and I wondered if there was some property of the filter I could take advantage of instead.

 

[VoG]

Perhaps like this

If IsNumeric(Application.Match(Range("$B$8").Value, Range("G66:G13900"), 0)) Then ActiveSheet.Range("$A$66:$G$13900").AutoFilter Field:=7, Criteria1:=Range("$B$8").Value

 

[Geoff Taylor]

Thank you, that's great. A follow-up question...

I now want to do a second test on a second filter in the table.

<Code>If IsNumeric(Application.Match("Y", Range("F66:F13900").SpecialCells(xlCellTypeVisible), 0)) Then</Code>

This code doesn't detect the visible "Y" cells. Testing the line in the "immediate" window gives an "Error 2042"

 

[VoG]

Sorry, I'm not sure how to handle that.