vba to return results of filter function into an array

Question

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 functioncan this be accomplished?&nbsp;thanksGaryexample in vbaSub 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&nbsp;

hansvogelaar · Accepted Answer

g_keramidas&nbsp;
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
&nbsp;

Forum Discussion

vba to return results of filter function into an array

2 Replies

Resources