Filter not working for second criteria

  • Hi Again!

    I wrote a filter in VBA for three columns in my spreadsheet but for some reason it refuses to ignore the second condition of the last filter to exclude the zero values. I'm kind of scratching my head as to why this is so.

    The first filter looks for blanks
    The second filter looks for "Result"
    The third filter gets rid of blanks and zeroes

    As always....thanks a million for the support...

    .AutoFilterMode = False
    ActiveSheet.Range("$A$24:$AS$16027").AutoFilter Field:=7, Criteria1:="="
    ActiveSheet.Range("$A$24:$AS$16027").AutoFilter Field:=3, Criteria1:= _
    ActiveSheet.Range("$A$24:$AS$16027").AutoFilter Field:=39, Criteria1:="<>", Operator:=xlFilterValues, Criteria2:="<>0"
  • Re: Filter not working for second criteria

    Rory answered your question in this post a while back... you need to use a helper column:


    Use an additional column with a formula that tries to match the value to your list of values, then filter on that column where there is no match. You cannot use an array in an autofilter where you want to exclude the array values (you can only include them).

    Matt Mickle
    Using Excel 2010,2013 & 2016

  • Re: Filter not working for second criteria

    Thanks guys...I realized that my operator was wrong and also that I can only filter for max two criteria per column without a helper column.

    Cheers for the quick responses...

Participate now!

Don’t have an account yet? Register yourself now and be a part of our community!