Excel VBA: Use an array (containing operator and numbers stored in variable) as criteria in autofilter

Question

I have a UserForm where users can select the range of numbers to filter the date from: ComboBox1 (select the operator, eg >=), Textbox3 (select number), ComboBox2 (select the operator, eg <=), , TextBox4 ((select number).

Furthermore, there is a Checkbox7 where users can tick to choose to include a text string in the filter too. (There is a combination of both strings and numbers in the column.)

Only the first 2 and last if/elseif condition works, the others do generate a filtered selection, but only the text string (if Checkbox7 = True) and numbers that meet the criteria in ComboBox2 & TextBox4 appear.

I don't understand how autofilter arrays with a range of numbers work. I'm also not sure whether to use xlAnd or xlOr when filtering a range of numbers with multiple criteria..

Any suggestions on how to make the code less verbose would be great.

Dim arr_Filter() As Variant
Dim arr_moreless() As Variant
Dim Age_Day As Integer: Age_Day = 10

arr_Filter = Array(ComboBox1 & TextBox3, ComboBox2 & TextBox4)
arr_moreless = Array(ComboBox2 & TextBox4, "No Date")

If ComboBox2.Value = "" And TextBox4.Value = "" And CheckBox7 = True Then
  FilterRange.AutoFilter Field:=Age_Day, Criteria1:=ComboBox1 & TextBox3, Operator:=xlOr, Criteria2:="No Date"

ElseIf ComboBox2.Value = "" And TextBox4.Value = "" And CheckBox7 = False Then
  FilterRange.AutoFilter Field:=Age_Day, Criteria1:=ComboBox1 & TextBox3, Operator:=xlFilterValues

ElseIf ComboBox1.Value = "<=" And ComboBox2.Value = ">=" And CheckBox7 = True Then
  FilterRange.AutoFilter Field:=Age_Day, Criteria1:=arr_Filter, Operator:=xlOr, Criteria2:="No Date"

ElseIf ComboBox1.Value = "<=" And ComboBox2.Value = ">=" And CheckBox7 = False Then
  FilterRange.AutoFilter Field:=Age_Day, Criteria1:=arr_Filter, Operator:=xlFilterValues

ElseIf ComboBox1.Value = ">=" And ComboBox2.Value = "<=" And CheckBox7 = True Then

  FilterRange.AutoFilter Field:=Age_Day, Criteria1:=ComboBox1 & TextBox3, Operator:=xlAnd, Criteria2:=arr_moreless

ElseIf ComboBox1.Value = ">=" And ComboBox2.Value = "<=" And CheckBox7 = False Then
  FilterRange.AutoFilter Field:=Age_Day, Criteria1:=ComboBox1 & TextBox3, Operator:=xlAnd, Criteria2:=ComboBox2 & TextBox4
End If

Variatus Variatus · Accepted Answer · 2017-10-04T10:03:59

This is the basic code you need for your endeavour. You should install it in the user form's code sheet. Then you construct the rest of your code to meet the requirements of this skeleton.

Option Explicit

Private Sub CmdOK_Click()
    ' 04 Oct 2017
    ' CmdOK is the OK button on your Userform

    Dim Fld As Long
    Dim Crit1 As Variant
    Dim Optor As XlAutoFilterOperator
    Dim Crit2 As Variant
    Dim NewFilter As Boolean

    Fld = 1
    Crit1 = "g"
    ' Optor = xlAnd
    ' Crit2 = ""
    ' NewFilter = True            ' False to apply a second filter (on another field)
                                ' on the already filtered sheet

    ApplyAutoFilter Fld, Crit1, Optor, Crit2, NewFilter
End Sub

Private Sub ApplyAutoFilter(ByVal Fld As Long, _
                            ByVal Crit1 As Variant, _
                            Optional ByVal Optor As XlAutoFilterOperator = xlAnd, _
                            Optional ByVal Crit2 As Variant, _
                            Optional ByVal NewFilter As Boolean = True)
    ' 04 Oct 2017
    ' "Optional" means you don't have to provide these arguments
    ' in the function call

    If IsEmpty(Crit2) Then Optor = xlOr
    With Worksheets("Sheet1")
        If NewFilter Then .AutoFilterMode = False

        ' specify the caption row: A1 = Field 1, B1 = Field 2, etc.
        With .Range("A1:K1")
            .AutoFilter Field:=Fld, _
                        Criteria1:=Crit1, _
                        Operator:=Optor, _
                        Criteria2:=Crit2
        End With
    End With
End Sub

Start by modifying the Sheet name and filter range in the Sub ApplyAutoFilter. After that you don't need to touch this procedure anymore.

Next step is to have an OK button on your user form which is called CmdOK. Now the CmdOK_Click procedure will run whenever you click that button.

There are 5 parameters you have to provide for each call on the filter. I made it that you only really need two, Fld (the cell number in the filter range, starting from 1 for the first cell) and Crit1, a first criterion. The other three will be set automatically why you don't need them. Start your trials with two and then expand step by step.

All parameters can be set dynamically (if you want). It seems that your Field number is Age_Day which doesn't need a variable because it seems not variable. But make sure that the range you have set in the filter sub does have 10 cells. If all you want is to filter on one columns the normal procedure is to set the range as "J1" which has only one cell, so that Fld = 1.

Your Crit1 appears to be a concatenation of a combobox and textbox. Write all your many IF conditions into the creation of this one parameter which, finally, you pass to the filter. Note that you can't pass an array as Crit2 (nor Crit1). You will have to extract the value you want to assign before you pass the argument.

I hope this puts you on your path to success. Good luck!

Excel VBA: Use an array (containing operator and numbers stored in variable) as criteria in autofilter

1 Answers