Page tree

Welcome to FreeSoftwareServers Confluence Wiki

Skip to end of metadata
Go to start of metadata

https://stackoverflow.com/a/5891317/5079799

Public UniqueValuesFromRangeInDictionary As Object
Public Sub Test()

Dim TestRng As Range
Set TestRng = ActiveSheet.Range("A1:A5")

Call GetUniqueAndCount(TestRng)

 For Each k In UniqueValuesFromRangeInDictionary.keys
  Debug.Print k, UniqueValuesFromRangeInDictionary(k)
 Next k
    
End Sub
Public Sub GetUniqueAndCount(c As Range)

Dim k, tmp As String

Set UniqueValuesFromRangeInDictionary = CreateObject("scripting.dictionary")
For Each c In c.Cells
 tmp = Trim(c.Value)
 If Len(tmp) > 0 Then UniqueValuesFromRangeInDictionary(tmp) = UniqueValuesFromRangeInDictionary(tmp) + 1
Next c

 'For Each k In UniqueValuesFromRangeInDictionary.keys
 ' Debug.Print k, UniqueValuesFromRangeInDictionary(k)
 'Next k

End Sub

  • No labels