How to evaluate this function in VBA?

Question

I am trying to use the below Excel formula in my VBA code but can't get it to work:

=Rows(Unique(Filter(myRange, myRange))

This function works as expected in Excel, and the range I am using is a column from a table (also working as expected). Not sure where I'm going wrong with this.

Dim ldRng As Range
Dim ans As Variant
Set ldRng = ActiveSheet.ListObjects("TimeTable").ListColumns("Load ID").DataBodyRange
                             
With Application
    ans = .Rows(.Unique(.Filter(ldRng, ldRng)))
End With
                              
If ans = 2 Then
                                
    ActiveCell.Value = ActiveCell.Offset(-1, 0).Value + 1
    ActiveCell.Offset(0, 1).Value = mi.ReceivedTime
    ActiveCell.Offset(0, 2).Value = Replace(fName, ".pdf", ext, 1)
                
End If

I am getting a mismatch error on the "ans =" line.

Answer 1

If you have only numbers and empty cells in this Load ID column you can use the code:

   Dim ldRng As Range
   Dim ans As Variant
   Set ldRng = ActiveSheet.ListObjects("TimeTable").ListColumns("Load ID").DataBodyRange
   With Application
      ans = UBound(.Unique(.Filter(ldRng, ldRng)))
   End With
 '  Debug.Print ans