VBA Excel Formula count unique filtered visible values (1 Viewer)

'   =SUM(IF(FREQUENCY(IF(SUBTOTAL(3,OFFSET(A6:A10000,ROW(A6:A10000)-ROW(A2),0,1)),MATCH("~"&A6:A10000,A6:A10000&"",0)),ROW(A6:A10000)-ROW(A6)+1),1))
   ' This one actually works it counts exactly the same unique values as the filter box without counting the blanks
   ' This formula assumes the data was pasted into cell A5 (e.g. the header is A5 through I5) and the data is A6:I10000
   ' Don't worry about blank cells. This formula will counter.
   ' To manually enter into an Excel workbook, Copy the formula above (with out the single quote in front
   ' Highlight cell A4, then past the formula into the formula bar.
   ' Then hold down Control+Shift and Enter. Excel will surround the formula with curly brackets to indicate it is an array formula.
   ' The code below is used for automation where
   ' Dim ObjXL As Excel.Application
   '     Set ObjXL = New Excel.Application
    ' and a new workbook is created - with copyfromrecordset starts at A5 (headers) and data starts at A6

'
    ObjXL.Range("A4").Select
    ObjXL.Selection.FormulaArray = _
        "=SUM(IF(FREQUENCY(IF(SUBTOTAL(3,OFFSET(R[2]C:R[9996]C,ROW(R[2]C:R[9996]C)-ROW(R[2]C),0,1)),MATCH(""~""&R[2]C:R[9996]C,R[2]C:R[9996]C&"""",0)),ROW(R[2]C:R[9996]C)-ROW(R[2]C)+1),1))"
    ObjXL.Range("A4").Select
    ObjXL.Selection.AutoFill Destination:=ObjXL.Range("A4:I4"), Type:=xlFillDefault
    ObjXL.Range("A3").Select
    ObjXL.ActiveCell.FormulaR1C1 = "Unique"
    ObjXL.Range("A3").Select
    ObjXL.Selection.AutoFill Destination:=ObjXL.Range("A3:I3"), Type:=xlFillDefault