Unique count on filtered data

[Danny54]

ws.Range("A3:H3000").Select
Selection.AutoFilter
wb.ActiveSheet.Range("$A$3:$H$275").AutoFilter Field:=5, Criteria1:= _
"=*Update*", Operator:=xlAnd






The following is the output after running the filter above




a b c d e
1
2
3 Name Title
5 D001 Update
6 D001 Update
7 D001 Update
10 BMOCADAPP01M001 Update




How can I get a unique count of col a? Everything I try gives me a count of the unfiltered stuff




Dim Server_Name As Range
Dim List As Object
Dim erow As Integer

Set List = CreateObject("Scripting.Dictionary")
erow = ActiveSheet.Cells(1, 1).CurrentRegion.Rows.Count + 1
erow = ActiveSheet.Cells(1, 1).Special

For Each Server_Name In ws.Range("A4:A" & erow)
If Not List.Exists(Server_Name.Value) Then List.Add Server_Name.Value, Nothing
Next

MsgBox List.Count


Thanks

 

[Peter_SSs]

would there be a simple way to set the .count field to 0 when this condition occurs? Im using the .Count field in a report

I would suggest making the two small changes shown in red below to Fluffs original code (then there is no need to check if the filter produced any names or not) and use .Count - 1 in your report (or set up a new variable and set its value to .Count - 1)

How about

Sub Danny54()
   Dim Cl As Range
   
   With CreateObject("scripting.dictionary")
      For Each Cl In Range("A[COLOR="#FF0000"][B][SIZE=4]3[/SIZE][/B][/COLOR]", Range("A" & Rows.Count).End(xlUp)).SpecialCells(xlVisible)
         .Item(Cl.Value) = Empty
      Next Cl
      MsgBox .Count [COLOR="#FF0000"][B][SIZE=4]- 1[/SIZE][/B][/COLOR]
   End With
End Sub