Thanks Claus,


Using the formula =MAX(COUNTIF(A$1:A$200,A1:A200)) on the worksheet, it tells me the max number of repeats is 12.

If I use 12 in F1 to set k to 12, the code errors on this line.

ReDim Preserve varOut(WorksheetFunction.CountA(.Range("B:B")) - 1, 0)

However, if I use 11 in F1 then the code runs fine, but the return is false as it says "10.33 = 11 times" when I know there are 12 "10.33"'s in column A.

I was trying to set up myMax in the code to prevent an error if F1 was greater than myMax.

But as it is now the error occurs if myMax = F1.

Howard


Sub AnyDupesNumF1()
Dim i As Long, lr As Long, j As Long, k As Long
Dim vArray As Variant, varOut() As Variant
Dim myMax As Long

[B:B,C:C].ClearContents

'With WorksheetFunction
' myMax = Evaluate("=Max(CountIf(A1:A200,A1:A200))")
'End With
' MsgBox myMax

With Sheets("Sheet1")
lr = .Cells(Rows.Count, "A").End(xlUp).Row
vArray = .Range("A1:A" & lr)
k = .Range("F1")

For i = 2 To UBound(vArray)
If vArray(i, 1) = vArray(i - 1, 1) Then
j = j + 1
If j = k Then
.Cells(i - 1, 2) = vArray(i, 1) & " = " & j & " times"
j = 0
End If
Else
j = 0
End If
Next 'i

lr = .Cells(Rows.Count, 2).End(xlUp).Row
vArray = .Range("B1:B" & lr)
k = 0

ReDim Preserve varOut(WorksheetFunction.CountA(.Range("B:B")) - 1, 0)
For i = LBound(vArray) To UBound(vArray)
If Len(vArray(i, 1)) 0 Then
varOut(k, 0) = vArray(i, 1)
k = k + 1
End If
Next
.Range("C1").Resize(k) = varOut
End With
End Sub