This functions input is an range and the output are an array of row numbers, it only returns those rows which has same value two or more times, aka duplicates + 1 occurrence.
As a one dimensional array it works fine, but I also want to add how many occurrence there are for each row, so i'm trying to transform the script to a dynamic two dimensional array (tempArray2).
But I can't get it work. I read somewhere that it's only possible to redim the last dimension, therefore "ReDim Preserve tempArray2(t)".
I get "subscript out of range" on row 12. Any pointers? Is it even possible?
Function DuplicateRow(rg As Range) As Variant
Dim tempArray1() As Variant 'temp array for rembers duplicats
Dim tempArray2() As Variant 'array which return all rows
For Each c In rg
If Not IsEmpty(c.value) Then
If Application.WorksheetFunction.CountIf(rg, c) > 1 Then
ReDim Preserve tempArray1(x)
If IsInArray(c.value, tempArray1) Then
'''ReDim Preserve tempArray2(y) '''1d-array works fine!
ReDim Preserve tempArray2(t)
'''tempArray2(y) = c.Row '''1d-array works fine!
tempArray2(y, t) = Cells(c.Row, Application.WorksheetFunction.CountIf(rg, c))
y = y + 1
t = t + 1
End If
tempArray1(x) = c.value
x = x + 1
End If
Next c
DuplicateRow = tempArray2()
End Function
Bookmarks