Count unique values in Excel

by

Here is a VBA function that works for me.

You can use it as a worksheet function, referencing any range, eg “=CountUnique(N8:O9)”

It handles text and numeric values, and treats blank cells as one value

It does not require dealing with array functions.

It requires a reference to the Microsoft Scripting Library, for the dictionary object.

    Public Function CountUnique(rng As Range) As Integer
        Dim dict As Dictionary
        Dim cell As Range
        Set dict = New Dictionary
        For Each cell In rng.Cells
             If Not dict.Exists(cell.Value) Then
                dict.Add cell.Value, 0
            End If
        Next
        CountUnique = dict.Count
    End Function

More Related Contents:

  1. Want to concatenate multiple row data in in cell along with its values if present. Emp Id should not come if no data available infromt of it
  2. Excel Number Separation [closed]
  3. Excel formula checklist in employees
  4. Sequence of numbers based on start and end value of 2 cells [closed]
  5. Error in finding last used cell in Excel with VBA
  6. Excel VBA – How to add dynamic array formula
  7. Filling any empty cells with the value above
  8. Find last used cell in Excel VBA
  9. Concatenate column headers if value in rows below is non-blank
  10. How do I apply a SumIf Formula in a VBA Sub? Getting Error 1004
  11. Countif With Multiple OR Criteria
  12. How to extract text within a string of text
  13. How to roundup a number to the closest ten?
  14. VBA comma separated cells, needt last entry only
  15. I have an Excel data sheet that I would like to extract data from into a new sheet
  16. Getting unique values in Excel by using formulas only
  17. Wait for shell command to complete [duplicate]
  18. How to return the number of dimensions of a (Variant) variable passed to it in VBA [duplicate]
  19. How to add a named sheet at the end of all Excel sheets?
  20. VBA general way for pulling data out of SAP [closed]
  21. Call a Sub with a String
  22. Delete Row based on Search Key VBA
  23. How to add a button programmatically in VBA next to some sheet cell data?
  24. return csv file as recordset
  25. Copy a selected range to another worksheet
  26. Benchmarking VBA Code
  27. The MacScript function is not working well in Office for Mac 2016! Any ideas?
  28. Application.Quit command not closing the entire Excel Application
  29. Trying to open the workbook in separate instance
  30. Function Overloading and UDF in Excel VBA

Leave a Comment