API reference · Arrays

Filter

Select rows using one or two criteria, combining them with AND or OR.

FunctionArrays→VariantSource L351

Signature#

Function Filter(ByVal vArr As Variant, _
                ByVal lFilterColumn1 As Long, ByVal ResearchType1 As ResearchType, ByVal vComparedValues1 As Variant, Optional ByVal IncludeBlank As Boolean = False, _
                Optional ByVal FilterType As FilterType = eAND, _
                Optional ByVal lFilterColumn2 As Long = -1, Optional ByVal ResearchType2 As ResearchType = eEqual, Optional ByVal vComparedValues2 As Variant = Empty) As Variant

Parameters#

Parameter	VBA type	Default	Description
`vArr`	`Variant`ByVal	Required	Allocated 1D or 2D data array. A 1D input is expanded to a column before normal filtering.
`lFilterColumn1`	`Long`ByVal	Required	Actual first criterion-column subscript. For an expanded 1D input, use 1.
`ResearchType1`	`ResearchType`ByVal	Required	First comparison operator. ResearchType values are eEqual=1000, eNotEqual=1001, eGreater=1002, eGreaterOrEqual=1003, eLess=1004, eLessOrEqual=1005, eTextInclude=1006, eTextExclude=1007.
`vComparedValues1`	`Variant`ByVal	Required	Scalar comparison value, 1D criterion array, or 2D single-row/single-column criterion vector. A scalar Empty bypasses filtering and returns the original array immediately.
`IncludeBlank`	`Boolean`ByVal	`False`	Controls handling of Empty data cells. With False, direct operators reject Empty, while inverse operators accept Empty. This flag does not classify zero-length strings as Empty.
`FilterType`	`FilterType`ByVal	`eAND`	FilterType.eAND=1 requires both criteria; FilterType.eOR=2 accepts either. An invalid value returns Empty unless the Empty-first-criterion bypass has already returned.
`lFilterColumn2`	`Long`ByVal	`-1`	Actual second criterion-column subscript. Validated only when vComparedValues2 is not Empty.
`ResearchType2`	`ResearchType`ByVal	`eEqual`	Second comparison operator; defaults to eEqual=1000.
`vComparedValues2`	`Variant`ByVal	`Empty`	Second scalar or criterion vector. Empty disables the second criterion, independently of FilterType.

Returns#

Variant containing matching rows in their original order. A normal successful filter always returns 2D with row bounds 1 To matchCount and the source column bounds. No matches and most invalid inputs return Empty. An Empty first criterion returns the original input array with its original representation and bounds.

Example#

SEE THE TRANSFORMATIONCategory = Sales AND amount ≥ 100

Before

transactions2D · 4 × 2

Rows 1..4 · Columns 1..2

row / col	1	2
1	“Sales”	120
2	“Fees”	15
3	“Sales”	80
4	“Sales”	200

After

selected2D · 2 × 2

Rows 1..2 · Columns 1..2

row / col	1	2
1	“Sales”	120
2	“Sales”	200

Only matching rows remain, in source order. Output row indices restart at 1.

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

Sub ExampleFilter()
    Dim a As New Arrays
    Dim transactions As Variant, selected As Variant

    ReDim transactions(1 To 4, 1 To 2)
    transactions(1, 1) = "Sales": transactions(1, 2) = 120
    transactions(2, 1) = "Fees": transactions(2, 2) = 15
    transactions(3, 1) = "Sales": transactions(3, 2) = 80
    transactions(4, 1) = "Sales": transactions(4, 2) = 200

    ' eEqual=1000; eAND=1; eGreaterOrEqual=1003
    selected = a.Filter(transactions, 1, 1000, "Sales", _
                        False, 1, 2, 1003, 100)
    If IsArray(selected) Then
        Debug.Print selected(1, 2), selected(2, 2) ' Expected: 120, 200
    End If
End Sub

Behavior and edge cases#

The input is not modified. Selected row indices are recorded first; the output matrix is allocated once and filled in source order.
Error-valued and Null data cells fail the criterion that reads them. With OR, another criterion can still select that row. Error-valued and Null criterion values are skipped.
Equality and ordering compare CDbl values when both sides are numeric and the data value has nonzero Len. Otherwise they compare UCase(CStr(...)) strings. Numeric strings can therefore equal numeric values, and ordinary text equality/order is case-insensitive.
eTextInclude and eTextExclude use InStr with vbBinaryCompare. Substring matching is case-sensitive and does not interpret wildcard patterns.
Multiple criterion values are evaluated with an any-match scan for eEqual, eLess, eGreater and eTextInclude. The inverse operators negate that aggregate result: eNotEqual excludes every listed equal value; eTextExclude rejects any listed substring. Multiple eGreaterOrEqual thresholds require being no less than every threshold, and multiple eLessOrEqual thresholds require being no greater than every threshold.
With IncludeBlank=False, IsEmpty data returns the operator's inversion flag. Empty cells therefore pass eNotEqual, eGreaterOrEqual, eLessOrEqual and eTextExclude, and fail eEqual, eLess, eGreater and eTextInclude. With IncludeBlank=True they proceed through the usual comparisons. Zero-length strings use the normal string path.
A full 2D criterion matrix cannot be reduced by the private criterion builder; the resulting non-array criterion makes CheckCriterion return True. An unsupported ResearchType is also not rejected and follows the inverse fallthrough. Supply supported operators and scalar/vector criteria instead of relying on these fallbacks.
The special Empty-first-criterion bypass occurs after IsArray but before rank, column and FilterType validation. It also prints 'No filter applied' to the Immediate window.

Performance#

O(r × (k1 + k2) + m × c) worst-case comparison/copy work for r rows, criterion-vector lengths k1 and k2, m matches and c columns. Short-circuiting may skip the second criterion. Storage is O(r) selected indices, O(k1 + k2) normalized criteria and O(m × c) output.

View implementation

Function Filter(ByVal vArr As Variant, _
                ByVal lFilterColumn1 As Long, ByVal ResearchType1 As ResearchType, ByVal vComparedValues1 As Variant, Optional ByVal IncludeBlank As Boolean = False, _
                Optional ByVal FilterType As FilterType = eAND, _
                Optional ByVal lFilterColumn2 As Long = -1, Optional ByVal ResearchType2 As ResearchType = eEqual, Optional ByVal vComparedValues2 As Variant = Empty) As Variant
    'Filters an array using up to two criteria with a single output allocation

    Dim vArr_Out                As Variant
    Dim vCriterionArray1        As Variant
    Dim vCriterionArray2        As Variant
    Dim lSelectedRows()         As Long
    Dim lRow                    As Long
    Dim lCol                    As Long
    Dim lCount                  As Long
    Dim lOut                    As Long
    Dim bCase1                  As Boolean
    Dim bCaseTotal              As Boolean
    Dim bArray2_Empty           As Boolean
    Dim vValue                  As Variant

    If Not IsArray(vArr) Then Exit Function
    If IsEmpty(vComparedValues1) Then
        Debug.Print ("No filter applied")
        Filter = vArr
        Exit Function
    End If

    Select Case FilterType
        Case eAND, eOR
        Case Else
            Exit Function
    End Select

    Select Case Me.DimCounter(vArr)
        Case 1
            vArr = Me.DimExpand(vArr)
        Case 2
        Case Else
            Exit Function
    End Select

    If lFilterColumn1 < LBound(vArr, 2) Or lFilterColumn1 > UBound(vArr, 2) Then Exit Function

    bArray2_Empty = IsEmpty(vComparedValues2)
    If Not bArray2_Empty Then
        If lFilterColumn2 < LBound(vArr, 2) Or lFilterColumn2 > UBound(vArr, 2) Then Exit Function
    End If

    vCriterionArray1 = BuildCriterionArray(lFilterColumn1, vComparedValues1)
    If Not bArray2_Empty Then vCriterionArray2 = BuildCriterionArray(lFilterColumn2, vComparedValues2)

    ReDim lSelectedRows(1 To UBound(vArr, 1) - LBound(vArr, 1) + 1)

    For lRow = LBound(vArr, 1) To UBound(vArr, 1)
        vValue = vArr(lRow, lFilterColumn1)
        If IsError(vValue) Or IsNull(vValue) Then
            bCase1 = False
        Else
            bCase1 = CheckCriterion(vValue, ResearchType1, vCriterionArray1, IncludeBlank)
        End If

        If bArray2_Empty Then
            bCaseTotal = bCase1
        ElseIf FilterType = eAND Then
            bCaseTotal = False
            If bCase1 Then
                vValue = vArr(lRow, lFilterColumn2)
                If Not IsError(vValue) And Not IsNull(vValue) Then
                    bCaseTotal = CheckCriterion(vValue, ResearchType2, vCriterionArray2, IncludeBlank)
                End If
            End If
        Else
            bCaseTotal = True
            If Not bCase1 Then
                vValue = vArr(lRow, lFilterColumn2)
                If IsError(vValue) Or IsNull(vValue) Then
                    bCaseTotal = False
                Else
                    bCaseTotal = CheckCriterion(vValue, ResearchType2, vCriterionArray2, IncludeBlank)
                End If
            End If
        End If

        If bCaseTotal Then
            lCount = lCount + 1
            lSelectedRows(lCount) = lRow
        End If
    Next lRow

    If lCount = 0 Then Exit Function

    ReDim vArr_Out(1 To lCount, LBound(vArr, 2) To UBound(vArr, 2))

    For lOut = 1 To lCount
        For lCol = LBound(vArr, 2) To UBound(vArr, 2)
            vArr_Out(lOut, lCol) = vArr(lSelectedRows(lOut), lCol)
        Next lCol
    Next lOut

    Filter = vArr_Out
End Function