Count unique values with criteria

[sharky12345]

I've got this formula which counts the unique values in column A on the condition that the text in column E matches the criteria NORTH:

=SUM(IF("NORTH"='AUG CR'!$E$2:$E$45453, 1/(COUNTIFS('AUG CR'!$E$2:$E$45453, "NORTH",'AUG CR'!$A$2:$A$45453, 'AUG CR'!$A$2:$A$45453)), 0))

I really need this to be done by VBA though as the workbook is already quite large and I'm trying to reduce the amount of formulas used.

Can anyone assist please?

 

[Yongle]

Try this

Sub CountUnique()
    Dim arr As Variant, i As Long, coll As New Collection, itm As String
    With ActiveSheet
        arr = .Range("A2", .Range("A" & .Rows.Count).End(xlUp)).Resize(, 5)
    End With
    For i = 1 To UBound(arr, 1)
        itm = UCase(arr(i, 5))
        If itm = "NORTH" Then
            itm = itm & arr(i, 1)
            On Error Resume Next
            coll.Add itm, itm
            On Error GoTo 0
        End If
    Next i
    MsgBox coll.Count, , "UNIQUE COUNT"
End Sub

 

[sharky12345]

That's just brilliant!

Thanks buddy!!