Solved Autofilter with 3 criteria

August 2, 2016 at 16:03:51
Specs: Windows 7
This should be so easy but I cannot get it to work; the code works except for the autofilter with the 3 criterias, I get Run-time error 1004 Application-defined or object-defined error. If i only have 2 criteria it works fine but I need the 3rd:

Sub PipelineShowKeithLoans()
'
ActiveSheet.Unprotect
If (ActiveSheet.AutoFilterMode And ActiveSheet.FilterMode) Or ActiveSheet.FilterMode Then
  ActiveSheet.ShowAllData
End If
    ActiveSheet.Range("$a$6:$AQ$1000").AutoFilter Field:=34, Criteria1:="<>Pre-Approval"

    ActiveSheet.Range("$A$7:$AV$1000").AutoFilter Field:=7, Criteria1:="<>DECL", _
        Operator:=xlAnd, Criteria2:="<>WITH", Operator:=xlAnd, Criteria3:="<>CLOSED"

   
ActiveSheet.Protect AllowFiltering:=True
End Sub

message edited by mecerrato


See More: Autofilter with 3 criteria

Report •


#1
August 2, 2016 at 16:56:25
OK, I'm going to answer this question "backwards" by telling you what doesn't work and let figure out what does.

This does not work:

ActiveSheet.Range("$A$7:$AV$1000").AutoFilter Field:=7, _
            Criteria1:=Array("<>DECL", "<>WITH", "<>CLOSED"), _
            Operator:=xlFilterValues

However, this does:

ActiveSheet.Range("$A$7:$AV$1000").AutoFilter Field:=7, _
            Criteria1:=Array("DECL", "WITH", "CLOSED"), _
            Operator:=xlFilterValues

Therefore, try the opposite of what you are trying. Build an array of the strings you want to see and then AutoFilter on the "positive" not the "negative".

Click Here Before Posting Data or VBA Code ---> How To Post Data or Code.


Report •

#2
August 2, 2016 at 17:30:34
I did think of think of that but the list of things I want to see changes, the things I don't want to see are always the same. So if I use that logic I would have to be mindful of new categories or changing categories. The ones I don't want to see will not change.

I also tried this but I get Run-time error 1004 Application-defined or object-defined error. If i only have 2 criteria it works fine but I need the 3rd:

ActiveSheet.Range("$A$7:$AV$1000").AutoFilter Field:=7, Criteria1:="<>DECL", _
        Operator:=xlAnd, Criteria2:="<>WITH", Operator:=xlAnd, Criteria3:="<>CLOSED"

message edited by mecerrato


Report •

#3
August 3, 2016 at 11:47:12
✔ Best Answer
I'm not sure why you marked the thread as solved if you are still having a problem. I have reset the Best Answer.

The following code seemed to work for me. What it does is loop through a range and build an array of values that are <> "DECL" and <> "WITH" and <> "CLOSED". The array will be populated with all of your "want to see" values. It then uses that array as the filter criteria.

Obviously it will need to be modified for your range and for the size of your data set.

Sub FilterArray()
Dim arr1(15) As String
Dim i As Integer
i = 1
'Populate array with "want to see" values
    For Each cell In Range("A1:A15")
      If cell <> "DECL" And cell <> "WITH" And cell <> "CLOSED" Then
        arr1(i) = cell
        i = i + 1
      End If
    Next
'Filter on array values
    Range("A1:A15").AutoFilter Field:=1, _
            Criteria1:=Array(arr1()), Operator:=xlFilterValues
End Sub

Click Here Before Posting Data or VBA Code ---> How To Post Data or Code.


Report •

Related Solutions

#4
August 8, 2016 at 16:09:22
I marked it solved because I was able to get a confirmation of the total list of things that I want to filter for so using the filter as you explained worked.

Report •

Ask Question