|
» Count unique values using VBA in Microsoft Excel
VBA macro tip contributed by
Erlandsen
Data Consulting offering Microsoft Excel Application development,
template customization, support and training solutions
|
|
 | |
CATEGORY: Custom Functions , Printing in VBA |
VERSIONS: All Microsoft Excel Versions |
|
The custom function below counts the unique values only from a given range. This function uses the fact that a collection can only hold items with unique identifications keys. If you try to add an item with a key that already exists in the collection, an error will occur.
Function CountUniqueValues(InputRange As Range) As Long
Dim cl As Range, UniqueValues As New Collection
Application.Volatile
On Error Resume Next ' ignore any errors
For Each cl In InputRange
UniqueValues.Add cl.Value, CStr(cl.Value) ' add the unique item
Next cl
On Error GoTo 0
CountUniqueValues = UniqueValues.Count
End Function
Example: =CountUniqueValues(A1:A100) will count the unique values in the range A1:A100. |
|
RATING:
4.06
VIEWS: 45533 |
||||
Sponsors:
 |
Book Store:
Recommended Books:
- Business Plans Kit for Dummies (With CD-ROM)
- Accounting for Dummies
- AWAKEN THE GIANT WITHIN : HOW TO TAKE IMMEDIATE CONTROL OF YOUR MENTAL, EMOTIONAL, PHYSICAL AND FINANCIAL
- Retire Young, Retire Rich
- The Accounting Game : Basic Accounting Fresh from the Lemonade Stand
- Dictionary of Finance and Investment Terms
Related MS EXCEL TIPS:
Terms
and Conditions of use
The applications/code on this site are distributed as is and without warranties
or liability. In no event shall the owner of the copyrights, or the authors
of the applications/code be liable for any loss of profit, any problems
or any damage resulting from the use or evaluation of the applications/code.
