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