0

I found on stackoverflow some code which I tried to adapt on my need, but with the classic error 9. This error (Subscript out of range) appeared after the second loop on Dim Preserve line with dupArrIndex = 2. I don't really understand why.

The code so far:

Function RemoveDuplicate(ByRef poArr As Variant) As Variant
Dim poArrNoDup() As Variant
Dim dupArrIndex As Integer
Dim i As Integer
Dim j As Integer
Dim dupBool As Boolean
dupArrIndex = 0 '-1
For i = LBound(poArr) To UBound(poArr)
    dupBool = False
    For j = LBound(poArr) To i
        If poArr(i, 1) = poArr(j, 1) And Not i = j Then
            dupBool = True
            Exit For
        End If
    Next j
    If dupBool = False Then
        dupArrIndex = dupArrIndex + 1
        ReDim Preserve poArrNoDup(1 To dupArrIndex, 1 To 5)
        poArrNoDup(dupArrIndex, 1) = poArr(i, 1)
        poArrNoDup(dupArrIndex, 2) = poArr(i, 2)
        poArrNoDup(dupArrIndex, 3) = poArr(i, 3)
        poArrNoDup(dupArrIndex, 4) = poArr(i, 4)
        poArrNoDup(dupArrIndex, 5) = poArr(i, 5)
    End If
Next i
RemoveDuplicate = poArrNoDup
End Function

What I want to achieve is to get from:

arr(1,1) = aaa
arr(1,2) = asdee
arr(1,3) = qwere
arr(2,1) = bbb
arr(2,2) = ghy
arr(2,3) = rfghdf
arr(3,1) = aaa
arr(3,2) = asdee
arr(3,3) = qwere

to

arr(1,1) = aaa
arr(1,2) = asdee
arr(1,3) = qwere
arr(2,1) = bbb
arr(2,2) = ghy
arr(2,3) = rfghdf

In other word to remove the identical rows on a 2D array (dimension (x,5)).

Thanks - JLuc01

JLuc01
  • 187
  • 1
  • 12
  • ...you beat me to it – Tim Williams Jan 06 '22 at 22:28
  • Thankfully you had a comment in the linked thread - copied its exact wording, well at least the end. – BigBen Jan 06 '22 at 22:29
  • OP: you could swap the dimensions (as mentioned in the linked thread) and then transpose at the end, but note that `Redim Preserve` is expensive and should be avoided in a loop if possible. – BigBen Jan 06 '22 at 22:31
  • 2
    Posted yet another answer to the post where you got your code from: https://stackoverflow.com/a/70614809/478884 – Tim Williams Jan 06 '22 at 23:21
  • Thanks Tim, it works perfectly. But I will need to have a closer look on the code for 2D as it is not simple, even if I understand it well for 1D. – JLuc01 Jan 07 '22 at 10:05

0 Answers0