How do I get a list of unique values from a range in Excel VBA?

by

I would use a simple VBA-Collection and add items with key. The key would be the item itself and because there can’t be duplicit keys the collection will contain unique values.

Note: Because adding duplicit key to collection raises error wrap the call to collection-add into a on-error-resume-next.

The function GetUniqueValues has source-range-values as parameter and retuns VBA-Collection of unique source-range-values. In the main method the function is called and the result is printed into Output-Window. HTH.

Sample source range looked like this:
enter image description here

Option Explicit

Sub main()
    Dim uniques As Collection
    Dim source As Range

    Set source = ActiveSheet.Range("A2:F6")
    Set uniques = GetUniqueValues(source.Value)

    Dim it
    For Each it In uniques
        Debug.Print it
    Next
End Sub

Public Function GetUniqueValues(ByVal values As Variant) As Collection
    Dim result As Collection
    Dim cellValue As Variant
    Dim cellValueTrimmed As String

    Set result = New Collection
    Set GetUniqueValues = result

    On Error Resume Next

    For Each cellValue In values
        cellValueTrimmed = Trim(cellValue)
        If cellValueTrimmed = "" Then GoTo NextValue
        result.Add cellValueTrimmed, cellValueTrimmed
NextValue:
    Next cellValue

    On Error GoTo 0
End Function

Output

SGD
PHP
KRW
CNY
IDA
BGN
PDSS
CBBT
INPC
DBS
a

In case when the source range consists of areas get the values of all the areas first.

Public Function GetSourceValues(ByVal sourceRange As Range) As Collection
    Dim vals As VBA.Collection
    Dim area As Range
    Dim val As Variant
    Set vals = New VBA.Collection
    For Each area In sourceRange.Areas
        For Each val In area.Value
            If val <> "" Then _
                vals.Add val
        Next val
    Next area
    Set GetSourceValues = vals
End Function

Source type is now Collection but then all works the same:

Dim uniques As Collection
Dim source As Collection

Set source = GetSourceValues(ActiveSheet.Range("A2:F6").SpecialCells(xlCellTypeVisible))
Set uniques = GetUniqueValues(source)

More Related Contents:

  1. Excel VBA – A general how to
  2. VBA code required for Msg Box when two value equal in two cells
  3. Find text and show value if condition is met
  4. Excel check len string separated by comma [closed]
  5. How can I insert variable into formula in VBA
  6. Wait until ActiveWorkbook.RefreshAll finishes – VBA
  7. Code in VBA loops and never ends. How to fix this?
  8. Excel column number from column name
  9. Pop up the Excel Statusbar?
  10. VBA – destroy a modeless UserForm instance properly
  11. How To Speed Up VBA Code
  12. Execute a command in command prompt using excel VBA
  13. “Unable to get the VLookup property of the WorksheetFunction Class” error [duplicate]
  14. Copy data from another Workbook through VBA
  15. Excel VBA Load worksheet in Function
  16. Searching an excel with two search words (multiple lookup) using vba macro [closed]
  17. Excel VBA – Combine rows with duplicate values in one cell and merge values in other cell
  18. Load csv file into a VBA array rather than Excel Sheet
  19. Change color of certain characters in a cell
  20. Want VBA in excel to read very large CSV and create output file of a small subset of the CSV
  21. VBA Macro crashes after 32000 rows
  22. How to create dynamic variable names VBA
  23. How to break a long string into multiple lines
  24. simple vba code gives me run time error 91 object variable or with block not set
  25. Iterating through all the cells in Excel VBA or VSTO 2005
  26. How can I display my windows user name in excel spread sheet using macros? [closed]
  27. How does Excel’s worksheet password protection work
  28. Hiding active workbook programmatically in Excel
  29. How to get selected value in multicolumn listbox
  30. Query my excel worksheet with VBA

Leave a Comment