excel-vba function to filter an array and return an array I have a function to filter a 2-d array.
It works. But I am not sure if the following mechanism is a sensible concept.
The idea is:

loop over the input array which is received as input_arr(y,x)

store the values in temp_arr(x,y), since in order to resize the y-dimension it
needs to be last dimension, so I have to transpose the values as I
store them

once the loop is done, transpose temp_arr(x,y) which
becomes the return value
Public Function filter_arr(input_arr As Variant, col_index As Long, filter_value As Variant) As Variant

 'the input_arr might be indexed starting from 0 or 1
 Dim n1 As Long, n2 As Long
 n1 = LBound(input_arr, 2)
 n2 = UBound(input_arr, 2)

 Dim temp_arr() As Variant
 Dim y As Long, x As Long, count As Long
 count = 0

 If (LBound(input_arr, 1) = 0) Then
  For y = LBound(input_arr, 1) To UBound(input_arr, 1)
   If (input_arr(y, col_index) = filter_value) Then

    ReDim Preserve temp_arr(n1 To n2, 0 To count)
    For x = n1 To n2
     temp_arr(x, count) = input_arr(y, x)
    Next x
    count = count + 1

  End If
 Next y

 Else

 'if LBound(input_arr, 1) = 1
 For y = LBound(input_arr, 1) To UBound(input_arr, 1)

  If (input_arr(y, col_index) = filter_value) Then

   count = count + 1
   ReDim Preserve temp_arr(n1 To n2, 1 To count)
   For x = n1 To n2
    temp_arr(x, count) = input_arr(y, x)
   Next x

   End If
  Next y

 End If

 filter_arr = Application.Transpose(temp_arr)

End Function



Edit: adding some context
Imagine that I have a ws in an .xlam file that contains product information which I've added as an add-in.
I have a function like =getProductData() which returns an array containing that data
So excel has a SORT function which works great with this kind of thing. I can do =SORT(getProductData(), 2, 1) to return my product data sorted by the 2nd column
But excel's FILTER function needs me to specify the data itself as its second parameter, not just its index, like in SORT
I can use INDEX to do something like =SORT(FILTER(getProductData(),INDEX(getProductData(),,3)=&quot;red&quot;,&quot;&quot;),2,1) to filter my product data by the 3rd column if the value = red, then sort by the 2nd column of the resulting array
But I was hoping to do something like
=SORT(filter_arr(getProductData(),3,&quot;red&quot;),2,1) to do the same
Naturally I'm still just playing around to determine what I can pull off - maybe using INDEX is the way to go and I'm trying to reinvent the wheel
ReDim Preserve and Transpose are not free.  I use this pattern when I need to filter an array:

Declare a 1-D array (Indices) with the same number of rows as the 2D array
When a match is found add the matches index to the Indices array and increment the counter
If there is matching data, declare a 2-D array (Results) with the same dimensions of the Input array but a row count equal to the counter
Iterate over the indices in the Indices array and add the matching values to the Results
Return the Results
If there are no matches return an empty array

Quick Filter
Public Function QuickFilter(input_arr As Variant, col_index As Long, filter_value As Variant) As Variant
    Dim r As Long
    Dim Count As Long
    Dim Indices As Variant
    ReDim Indices(LBound(input_arr, 1) To UBound(input_arr, 1))
    Count = LBound(input_arr, 1) - 1
    For r = LBound(input_arr, 1) To UBound(input_arr, 1)
        If input_arr(r, col_index) = filter_value Then
            Count = Count + 1
            Indices(Count) = r
        End If
    Next
    
    Dim Results As Variant
    Dim c As Long
    Dim index As Long
    If Count &gt;= LBound(input_arr, 1) Then
        ``` we want results to hold the entire row
        ReDim Results(LBound(input_arr, 1) To Count, LBound(input_arr, 2) To UBound(input_arr, 2))
        For r = LBound(input_arr, 1) To Count
            index = Indices(r)
            For c = LBound(input_arr, 2) To UBound(input_arr, 2)
                Results(r, c) = input_arr(index, c)
            Next
        Next
        QuickFilter = Results
    Else
        QuickFilter = Array()
    End If
End Function