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

 

[Fluff]

How about

Sub Danny54()
   Dim Cl As Range
   
   With CreateObject("scripting.dictionary")
      For Each Cl In Range("A4", Range("A" & Rows.Count).End(xlUp)).SpecialCells(xlVisible)
         .Item(Cl.Value) = Empty
      Next Cl
      MsgBox .Count
   End With
End Sub

 

[Danny54]

sometimes you can't see the forest for the trees.

that worked.

Thanks So Much!

 

[Fluff]

You're welcome & thanks for the feedback

 

[Danny54]

Just noticed that when my filter runs and returns no actual data the .Count shows 1 found.
any ideas why?

 

[Fluff]

If there are no visible cells, it will be counting the header in row 3

 

[Danny54]

It looks like this code counts the Name Labels as a field being selected? Any way of ignoring the Name Labels?

For Each Cl In Range("A4", Range("A" & Rows.Count).End(xlUp)).SpecialCells(xlVisible)
.Item(Cl.Value) = Empty
Next Cl

 

[Danny54]

Yep, thats exactly what its doing. My goal would be to only count actual data rows, so effectively the .Count field would be 0

 

[Fluff]

Simply add this line at the beginning of the code

   If Range("A" & Rows.Count).End(xlUp).Row = 3 Then Exit Sub

 

[Danny54]

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