locked
Using Excel autofilter with multiple criteria RRS feed

  • Question

  • Hi all,

    I'm pretty new to VBA, so please forgive my ignorance. I've been trying to filter some data on multiple criteria using the autofilter in Excel. See code extract below.

    Sub FilterNonWindowsDevices(ByVal SheetB As Worksheet, ByVal SheetC As Worksheet)
        'Enable filters
        SheetB.Range("B8:Q8").AutoFilter
        
        'Sort OS A-Z
        With SheetB.AutoFilter
            .Sort.SortFields.Clear
            .Sort.SortFields.Add Key:=Range("M8"), SortOn:=xlSortOnValues, Order:=xlAscending, DataOption:=xlSortNormal
            .Sort.Apply
        End With
        
        'Filter on OS names that contain *windows*
        SheetB.Range("B8:Q8").AutoFilter Field:=12, Criteria1:=Array("<>*windows*", "<>*X'*", "<>"), Operator:=xlFilterValues '"=" matches blanks, "<>" matches non-blanks
        'INDIVIDUALLY they work, combined they don't
        'SheetB.Range("B8:Q8").AutoFilter Field:=12, Criteria1:="<>*windows*"
        'SheetB.Range("B8:Q8").AutoFilter Field:=12, Criteria1:="<>*X'*"
        'SheetB.Range("B8:Q8").AutoFilter Field:=12, Criteria1:="<>"
    End Sub

    When the filter is used as above with multiple criteria, I get the error "AutoFilter method of Range class failed". When the filters are applied seperately, only the last one remains, which is logical. So I would like to find a way to pass the AutoFilter an array of criteria.

    Can someone help me out here? Many thanks in advance!

    Monday, March 26, 2012 7:25 AM

Answers

  • When you specify xlFilterValues as operator, you can only tell Excel what to include, not what to exclude. So

    ..., Criteria1:=Array("*windows*", "*X'*"), ...

    should work, but it'll fail if you add <> to the conditions. With <> in the conditions, you have to use xlAnd (or xlOr) as operator, and you'll be able to specify up to two conditions only, in Criteria1 and Criteria2.

    For more complex conditions, you'll need to use AdvancedFilter with a Criteria range.


    Regards, Hans Vogelaar

    • Proposed as answer by danishani Wednesday, April 4, 2012 12:51 AM
    • Marked as answer by danishani Monday, April 9, 2012 2:32 AM
    Monday, March 26, 2012 8:14 AM

All replies

  • When you specify xlFilterValues as operator, you can only tell Excel what to include, not what to exclude. So

    ..., Criteria1:=Array("*windows*", "*X'*"), ...

    should work, but it'll fail if you add <> to the conditions. With <> in the conditions, you have to use xlAnd (or xlOr) as operator, and you'll be able to specify up to two conditions only, in Criteria1 and Criteria2.

    For more complex conditions, you'll need to use AdvancedFilter with a Criteria range.


    Regards, Hans Vogelaar

    • Proposed as answer by danishani Wednesday, April 4, 2012 12:51 AM
    • Marked as answer by danishani Monday, April 9, 2012 2:32 AM
    Monday, March 26, 2012 8:14 AM
  • Thanks Hans for the reply, I also noticed that the error came from the "<>" signs in the criteria. I do need to exclude data from the filter, so I'll look into using the AdvancedFilter. It's stupid that the criteria are limited to two though :(.
    Monday, March 26, 2012 9:05 AM