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

@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

@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