Count Distinct in VBA

[rdw72777]

Is there a simple way to count distinct values in a range (not a pivot, just normal cells in a range) via VBA, or do i need to do the SumProduct/Countif function.

Seems odd, i just want to know the number of unique values in a rnage, but i'm spinning my wheels tryign to do this without putting in a worksheetfuntion = Sumproduct....line. Seems like VBA should have a prettier way but its eluding me.

 

[Saagar]

See if this helps -
http://office.microsoft.com/en-us/e....aspx#BMcount_the_number_of_unique_values_by_

 

[rdw72777]

I was just hoping VBA had something similar, I keep searching and seems like not. I really struggle with worksheet functions so try to avoid them...oh well. Thanks.

 

[taurean]

Macro Recorder + Edited code using Advanced filter!

Public Sub GetMeUniques()
Range("A1:A" & Cells(Rows.Count, "A").End(xlUp).Row).AdvancedFilter _
Action:=xlFilterCopy, CopyToRange:=Range("B1"), Unique:=True
End Sub

 

[rdw72777]

Sadly, my data is in rows across the top of columns. I'm probably makign a mountain out of a molehill. gonna kill off this thread and just ask for help converting to a worksheet function.

Thanks,

 

[taurean]

Never mind here's another one & you can adopt this for a specific range:

Public Sub CountUniquesVBA()
Dim ncUnique As New Collection
Dim lCount As Long
On Error Resume Next
For i = 1 To Range("A" & Rows.Count).End(xlUp).Row
ncUnique.Add Item:=Range("A" & i).Value, Key:=CStr(Range("A" & i).Value)
Next i
lCount = ncUnique.Count + 1
MsgBox lCount
End Sub

 

[Jonmo1]

Is there a simple way to count distinct values in a range (not a pivot, just normal cells in a range) via VBA, or do i need to do the SumProduct/Countif function.

Seems odd, i just want to know the number of unique values in a rnage, but i'm spinning my wheels tryign to do this without putting in a worksheetfuntion = Sumproduct....line. Seems like VBA should have a prettier way but its eluding me.

Simple, No...

Try something like

Sub UniqueCount()
Dim lr As Long, UniqueCount As Long, c As String, ws As Worksheet
Set ws = Sheets("Sheet1") 'Specify your sheet name here
c = "A" 'Spcify the column to count unique entries from
 
lr = ws.Cells(Rows.Count, c).End(xlUp).Row
With ws.Range(ws.Cells(1, c), ws.Cells(lr, c))
    .AdvancedFilter Action:=xlFilterInPlace, CriteriaRange:=.Range("A1:A296"), Unique:=True
    UniqueCount = .SpecialCells(xlCellTypeVisible).Count
    ws.ShowAllData
End With
MsgBox "There are " & UniqueCount & " Unique entries in column " & c
End Sub