VBA Custom Filter

[MikeDBMan]

I am trying to do a filter where either I select records where field 18 equals the variable Ans and field 22 equals Ans4 OR I want to select records where field 30 equals the text "Other"

                ActiveSheet.ListObjects("Table1").Range.AutoFilter Field:=18, Criteria1:="=" & Ans, Operator:=xlAnd, Field:=22, Criteria2:="=" & Ans4, Operator:=xlOr, Field:=30, Criteria1:="Other"

and my error message is wrong number of arguments or invalid property assignment (runtime error 450). Suggesions?

 

[DanteAmor]

You can only put an operator.

https://docs.microsoft.com/en-us/office/vba/api/excel.range.autofilter



It may be so:

    ActiveSheet.ListObjects("Table1").Range.AutoFilter Field:=18, Criteria1:=ans
    ActiveSheet.ListObjects("Table1").Range.AutoFilter Field:=22, Criteria1:=ans4
    ActiveSheet.ListObjects("Table1").Range.AutoFilter Field:=30, Criteria1:="Other"

 

[MikeDBMan]

That solutions tries to do all 3 as the first criteria AND the second criteria AND the third criteria. But I need it more like this: (Field 18 Criteria=ans AND Field 22 Criteria=ans4) OR Field 30 Criteria="Other". I can't figure how to phrase that.

 

[DanteAmor]

But with autofilter you can only use one field at a time:

expression. AutoFilter( Field , Criteria1 , Operator , Criteria2 , SubField , VisibleDropDown)

I understand your request, but the way is not the autofilter, maybe you should do the filter using code, for example:

    Application.ScreenUpdating = False
    ActiveSheet.ListObjects("Table1").DataBodyRange.EntireRow.Hidden = False
    col18 = ActiveSheet.ListObjects("Table1").DataBodyRange.Columns(18).Column
    col22 = ActiveSheet.ListObjects("Table1").DataBodyRange.Columns(22).Column
    col30 = ActiveSheet.ListObjects("Table1").DataBodyRange.Columns(30).Column
    For Each f In ActiveSheet.ListObjects("Table1").DataBodyRange.Rows
        If (Cells(f.Row, col18).Value = ans And Cells(f.Row, col22).Value = ans4) Or _
            Cells(f.Row, col30).Value = "Other" Then
        Else
            Rows(f.Row).EntireRow.Hidden = True
        End If
    Next
    Application.ScreenUpdating = True

 

[Mentor82]

Hi,
Why don't you consider adding an additional 'filter' column to which you'll return information whether the record os to be filtered or not based on if the conditions you mentioned are met or not. Having such information in addtional column you can use standard excel filtering method.

Regards,
Sebastian