Count duplicate values in a selection
in the development environment, set s reference (Tools/References) to the
Microsoft Scripting Runtime DLL. We'll use the collection dictionary to count
the items
add this code
Option Explicit
Sub GetDuplicates()
Dim dic As Scripting.Dictionary
Dim cell As Range
' this is tehy results container
Set dic = New Scripting.Dictionary
' gather the results
For Each cell In Selection.Cells
If dic.Exists(cell.Value) Then
dic.Item(cell.Value) = dic.Item(cell.Value) + 1
Else
dic.Add cell.Value, 1
End If
Next
' output the results
Dim ws As Worksheet
Dim rw As Long
Set ws = Worksheets.Add
For rw = 1 To dic.Count
ws.Cells(rw, 1) = dic.Keys(rw - 1)
ws.Cells(rw, 2) = dic.Items(rw - 1)
Next
Set dic = Nothing
End Sub
"Abdul Shakeel" wrote:
Dear All,
I want to count in a msgbox all duplicate values in selection through a
macro any ideas??
--
Regards,
|