Forum Discussion

Brass Contributor

Feb 19, 2023

Solved

vba to return results of filter function into an array

i'm wondering if this is possible. i currently have to enter a formula in a cell and then work with the date in that range. i can get the results of the unique function into an array, but not the filter function

can this be accomplished?

thanks

Gary

example in vba

Sub test()
Dim arr As Variant
Dim arr2 As Variant

' this works
arr = WorksheetFunction.Unique([Table1[Line]])

'this formula in a cell returns data, if empty excluded for simplicity
'=FILTER(Table1[Line],Table1[Form_Num] = 4)

'this pops a type mismatch error
arr2 = WorksheetFunction.Filter([Table1[Line]], [Table1[FORM_NUM]] = 4)
End Sub

You have to create an array of True/False values and pass that as second argument to WorksheetFunction.Filter:

Sub test()
    Dim arr2 As Variant
    Dim arr3 As Variant
    Dim i As Long
    Dim n As Long

    n = [Table1[Form_Num]].Count
    ReDim arr3(1 To n, 1 To 1)
    For i = 1 To n
        arr3(i, 1) = (Range("Table1[Form_Num]")(i) = 4)
    Next i

    arr2 = WorksheetFunction.Filter([Table1[Line]], arr3)
End Sub

4 Replies

HansVogelaar
MVP
Feb 19, 2023
g_keramidas

You have to create an array of True/False values and pass that as second argument to WorksheetFunction.Filter:

Sub test() Dim arr2 As Variant Dim arr3 As Variant Dim i As Long Dim n As Long n = [Table1[Form_Num]].Count ReDim arr3(1 To n, 1 To 1) For i = 1 To n arr3(i, 1) = (Range("Table1[Form_Num]")(i) = 4) Next i arr2 = WorksheetFunction.Filter([Table1[Line]], arr3) End Sub
- Alan_Newell
  Copper Contributor
  Aug 27, 2025
  Hello Hans, I have used this solution also.
  I have one question: How does the True/False in arr3 get connected to the Filter Function. I can see that arr3 is holding True/False values but I cannot work out how the Filter Function knows which data line the arr3 is associated with. Is it just because the line numbers "line up" ie there is no software link?
  Thanks
  Alan
  - HansVogelaar
    MVP
    Aug 27, 2025
    Yes, the comparison is purely line by line.
    
    FILTER includes the n-th item of Table1[Line] if the n-th item of arr3 is True.
- g_keramidas
  Brass Contributor
  Feb 20, 2023
  thanks, hans. just wondered why one array formula works and the other doesn't. i would have never thought of your solution.