Count unique records in a filtered range

Hi Leith, can I ask one more question please?

Does having blanks in the range affect the results? I only ask as if I filter by XML in column 2, there are 40 results in column 1 with 14 unique records, but the function you helped me with returns a result of 124?

Thanks once again

Hello jiminic,

The macro ignores cells that are blank. If a cell contains one or more spaces, it is not considered blank. I have updated the macro to ignore cells that may contain only spaces.

Function GetUniqueCount(Rng As Range)

   Dim Cell As Range
   Dim Cnt As Long
   Dim DSO As Object
        
    'Use only the first column of the selected range
     Set Rng = Rng.Resize(Rng.Rows.Count, 1)
     
     Set DSO = CreateObject("Scripting.Dictionary")
     DSO.CompareMode = 1
     
       For Each Cell In Rng
         If Trim(Cell) <> "" Then
           If Not DSO.Exists(Cell.Text) Then
              DSO.Add Cell.Text, Cell
              Cnt = Cnt + 1
           End If
         End If
      Next Cell
    
    Set DSO = Nothing
    GetUniqueCount = Cnt
    
End Function

Sincerely,
Leith Ross