Guides

Filter multiple criteria

Select records by one or two conditions, or by a list of accepted values.

Combine two conditions#

Filter accepts two column conditions joined with AND or OR. Column arguments are actual array indices, not worksheet column numbers. In the example, the source is 0-based and its Status column is index 2.

SEE THE TRANSFORMATIONRegion = North AND status = Posted

Before

records2D · 4 × 3

Rows 0..3 · Columns 0..2

row / col	0	1	2
0	“North”	120	“Posted”
1	“South”	80	“Posted”
2	“North”	50	“Pending”
3	“North”	200	“Posted”

After

selected2D · 2 × 3

Rows 1..2 · Columns 0..2

row / col	0	1	2
1	“North”	120	“Posted”
2	“North”	200	“Posted”

Columns remain indexed 0..2; matching output rows are indexed 1..2.

Shaded headers are array indices, not data. Quoted cells are text. Values shown are derived from the source, not an Excel execution.

Sub FilterPostedNorth()
    Const OP_EQUAL As Long = 1000 ' ResearchType.eEqual
    Const FILTER_AND As Long = 1  ' FilterType.eAND
    Dim records As Variant, selected As Variant
    records = Arr.DimMerge(VBA.Array( _
        VBA.Array("North", 120, "Posted"), _
        VBA.Array("South", 80, "Posted"), _
        VBA.Array("North", 50, "Pending"), _
        VBA.Array("North", 200, "Posted")))
    ' DimMerge of VBA.Array produces 0-based rows and columns.
    selected = Arr.Filter(records, 0, OP_EQUAL, "North", _
        False, FILTER_AND, 2, OP_EQUAL, "Posted")
    If Not IsArray(selected) Then Exit Sub
    Debug.Print selected(1, 1) ' Expected: 120
    Debug.Print selected(2, 1) ' Expected: 200
End Sub

Match a set of values#

Pass a scalar, 1D array, or a 2D single-row/single-column vector as a criterion. Positive operators match any criterion. Negative and inclusive comparison operators invert the combined underlying match: for example, eNotEqual excludes all listed values, while eGreaterOrEqual with multiple thresholds requires the value to meet every threshold.

Sub FilterRegions()
    Const OP_EQUAL As Long = 1000 ' ResearchType.eEqual
    Dim records As Variant, selected As Variant
    records = Arr.DimMerge(VBA.Array( _
        VBA.Array("North", 120), VBA.Array("South", 80), _
        VBA.Array("West", 45)))
    selected = Arr.Filter(records, 0, OP_EQUAL, _
        VBA.Array("North", "West"))
    If IsArray(selected) Then Debug.Print UBound(selected, 1) ' Expected: 2
End Sub

Check the result before indexing#

No matches returns Empty, not an allocated array with zero rows. Matching results are 2D, with row bounds starting at 1 and the original column bounds. An Empty first criterion returns the original array immediately. Null or error cells in a filtered column fail that condition, although an OR condition on another column can still keep the row.

With IncludeBlank=False, Empty cells fail positive operators but pass inverse operators. An empty string is not an Empty Variant. Equality and ordering convert numeric pairs to Double and otherwise compare uppercased strings. Substring matching uses case-sensitive vbBinaryCompare. General rectangular criterion matrices are unsupported; the helper’s failed reduction can make a condition accept every ordinary value.