Test for dups in Array
Very cool code. Thanks very much.
"KL" wrote:
Hi Perico,
How about something like this:
Sub finddups()
Dim c As Range
Dim D As Object
Dim Values As Variant
Dim Times As Variant
Set D = CreateObject("Scripting.Dictionary")
With Worksheets("Master")
For Each c In .Range("b28", .Cells(.Rows.Count, "B").End(xlUp))
MyStr = c & c.Offset(, 1) & c.Offset(, 2) & c.Offset(, 6)
If D.Exists(MyStr) Then
D.Item(MyStr) = D.Item(MyStr) + 1
Else
D.Add MyStr, 1
End If
Next c
With Application
Values = .Transpose(D.Keys)
Times = .Transpose(D.Items)
End With
.Range("K28").Resize(UBound(Values)) = Values
.Range("L28").Resize(UBound(Values)) = Times
End With
End Sub
Regards,
KL
"Perico" wrote in message
...
I need help on how do I test for duplicate values in an array, x()?
My code so far:
Sub finddups()
Dim mstrWks As Worksheet
Dim myRng As Range
Dim x(), i As Long, j As Long
Set mstrWks = Worksheets("Master")
With mstrWks
Set myRng = .Range("b28", .Cells(.Rows.Count, "B").End(xlUp))
End With
j = myRng.Rows.Count
i = 1
With myRng
For j = 1 To j
ReDim x(j)
x(j) = .Cells(i, 1) & .Cells(i, 2) & .Cells(i, 3) & .Cells(i, 7)
i = i + 1
Next j
End With
End Sub
|