VBA Autofilter 3 Criteria

[Oxalate]

Hello everyone,

I am new here and i am asking if anyone could please help me with my autofilter problem.
The thing is, i have a rather large Excel file with projects in it, and i'm trying to make it work so that if in any of the projects in the Status column are set as CA (cancelled) or RJ (rejected), all rows should be filtered out and not shown (this would make the table easier to navigate and read).

My problem is that in the code there is already one criteria, for projects set as CP (completed), this part has to remain in the code as it is:

Sub Filter()
ActiveSheet.Range("$A$7:$CD$1500").AutoFilter Field:=15, Criteria1:="<>CP"
End Sub

I have searched everywhere on google , i have also tried out myself but no luck. If i try something like this:

Sub Filter()
ActiveSheet.Range("$A$7:$CD$1500").AutoFilter Field:=15, Criteria1:="<>CP"
ActiveSheet.Range("$A$7:$CD$1500").AutoFilter Field:=15, Criteria2:=Array ("<>CA", "<>RJ"), operator:=xlOr
End Sub

this only filters the last entry in the array table (so if the last entry is RJ, only projects with RJ status will be filtered, and if the last entry is CA, only CA gets filtered).


And if I use the filtervalues operator, I always get the 1004 Error

Sub Filter()
ActiveSheet.Range("$A$7:$CD$1500").AutoFilter Field:=15, Criteria1:="<>CP"
ActiveSheet.Range("$A$7:$CD$1500").AutoFilter Field:=15, Criteria2:=Array ("<>CA", "<>RJ"), operator:=xlFiltervalues
End Sub

Can anyone help me, I would appreciate it.

Regards,
Oxalate

[mana]

You can use advancedfilter instead of autofilter.

[Oxalate]

Thank you for the reply but i don't know what do you exactly mean. Could you please write the code example? it just has to be values in this one field (Field:=15).

[mana]

Please try this.

Option Explicit


Sub test()
    Dim r As Range
    Dim c As Range


    Set r = Range("A1").CurrentRegion
    Set c = r.Resize(2, 3).Offset(, r.Columns.Count + 1)
    
    c.Rows(1).Value = r.Cells(1, 15).Value
    c.Rows(2).Value = Array("<>CP", "<>CA", "<>RJ")


    r.AdvancedFilter xlFilterInPlace, c
    c.ClearContents
    
End Sub

[mana]

if you use autofilter

Option Explicit


Sub test2()
    Dim dic As Object, a, v, i As Long, e
    
    a = Array("CP", "CA", "RJ")
    
    Set dic = CreateObject("scripting.dictionary")
     
    With Range("a1").CurrentRegion
        v = .Columns(15).Value
        For i = 1 To UBound(v)
            dic(v(i, 1)) = Empty
        Next
        For i = 0 To UBound(a)
        If dic.exists(a(i)) Then dic.Remove a(i)
        Next
        .AutoFilter
        .AutoFilter 15, dic.keys, xlFilterValues
    End With
  
End Sub

[Oxalate]

Dear Mana,

Sorry it did not work.
I tried to use the option 1 with the advanced filter, nothing gets filtered.
And for option 2 as soon as i try to use xlFiltervalues every time i get the same error: Run time error 1004 autofilter method of range class failed.

The entire code combined with the macro button looks like this:

Sub UserName()
ActiveSheet.Range("$A$7:$CD$1500").AutoFilter Field:=5, Criteria1:="*UserName*"
ActiveSheet.Range("$A$7:$CD$1500").AutoFilter Field:=15, Criteria1:="<>CP"
End Sub

The first filter filters the projects according to the team member (names are given in the column 5) that is in charge for those projects, and all i would like is to add "RJ" and "CA" to the second filter criteria in the field 15 so that all projects that are completed, rejected or cancelled are filtered. Is that possible?

[mana]

Option Explicit


Sub test3()
    Dim r As Range
    Dim c As Range
     
    Set r = Range("A7")
    Set r = Range(r, r.CurrentRegion(r.CurrentRegion.Count))
    Set c = r.Resize(2, 3).Offset(r.Rows.Count + 1)
     
    c.Cells(1, 1).Value = r.Cells(1, 5).Value
    c.Cells(1, 2).Value = r.Cells(1, 15).Value
    c.Cells(1, 3).Value = r.Cells(1, 15).Value
    c.Rows(2).Value = Array("CP", "<>CA", "<>RJ")


    r.AdvancedFilter xlFilterInPlace, c
    c.ClearContents
     
End Sub

[Oxalate]

i am sorry but it doesn't work. When i copy your code and use the macro button, everything is filtered, there are no projects shown at all.
I have tried adjusting the range from your "A7" to the "$A$7:$CD$1500", i have even tried to adjust the range to just the column with the filters values, that being "$O$11:$O$100" but no luck.

[mdmackillop]

Can you post a workbook with sample data?

[Oxalate]

I will do it later today, have to go to work now.
Thank you

[Oxalate]

Hello, please don't be mad, I have edited the file in order to hide sensitive data, and have used random place holder instead, but the file structure is the same.

The deal is on the top of the file there are 3 buttons (user1, user2 and user3). The code for those buttons us written under Module3 in VBA.
When a user is selected only the projects where he is the contact are shown. In the Column "N" there is Project Status e.g. CA or LI or RJ (those statuses can be changed at any time).
What i would like to achieve is that all projects that are with the status CP (complete), CA (cancelled) or RJ (rejected) are automatically filtered and not shown (hidden) when a user is selected , but still shown normally when "All" button is used.

Project.xlsm

[mdmackillop]

Have a look at the attached

[Oxalate]

Wow this look amazing, this is exactly what i had in mind thank you so much.
Just one more question i promise . Could it also be done so that there is still a manual filter present at all times on the cells?
I have added a manual filter that also filters other criteria (e.g. Code, Owner) but every time i click on a User button that filter disappears. Is it possible that both options "co-exist" i.e. so that on User click, all his projects are filtered as described in the posts above, but a manual filter is also present at all times for additional filtering options?

VBAX_59308_Project_MF.xlsm

[mdmackillop]

You cannot combine autofilter an advanced filter. This attachment hides rows rather than filtering which allows an autofilter to be used.

[KptKloss]

Hi guys

not sure if it helps any people looking for a solution in the future but I used this code and it worked well for me to set up multiple criteria in separate columns:

'here is 1 criterion in column 8
ActiveSheet.Range("$B$4:$HB$3016").AutoFilter field:=8, Criteria1:="TBD"
'here are multiple criteria in column 18
ActiveSheet.Range("$B$4:$HB$3016").AutoFilter field:=18, Criteria1:=Array("Started", "On hold", "Not started"), Operator:=xlFilterValues

hope this helps someone