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 fil...

excel

Macros and VBA

HansVogelaar

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

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

g_keramidas

Brass Contributor

Feb 19, 2023

thanks, hans. just wondered why one array formula works and the other doesn't. i would have never thought of your solution.

Forum Discussion

vba to return results of filter function into an array