CountIf() Function From VBA

Question

I am attempting to use the CountIf() function from my VBA syntax, but I get an error of

Wrong number of arguments or invalid property assignment

This is my syntax - what do I need to change so this is successful?

Dim countofemps As Integer

Sheets("All emps").Select
Selection.AutoFilter
ActiveSheet.ListObjects("Allemps").Range.AutoFilter Field:=1, Criteria1:=filtervalue
ActiveSheet.ListObjects("Allemps").Range.AutoFilter Field:=5, _
    Criteria1:=">=1/29/2017", Operator:=xlAnd, Criteria2:="<=3/1/2017"
'This line throws error
countofemps = WorksheetFunction.CountIf(Range("'All emps'!A2:A2000"), filtervalue, Range("'All emps'!B2:B2000"))

Answer 1

I'd go as follows:

With Sheets("All emps") '<--| reference your sheet
    With .ListObjects("Allemps").Range '<--| reference its "Allemps" table range
        .AutoFilter '<--| clear any previous filter
        .AutoFilter Field:=1, Criteria1:=filtervalue '<--| filter referenced range on its first column with given filtervalue
        .AutoFilter Field:=5, Criteria1:=">=1/29/2017", Operator:=xlAnd, Criteria2:="<=3/1/2017" '<--| filter reference range on its fifth column with given limit dates
        countofemps = Application.WorksheetFunction.Subtotal(103, .Columns(1)) -1  '<--| count the number of filtered cells on its first column except header cellr
    End With
End With