Remove duplicates from an array

[L. Howard]

All the code preceding this line work just fine, and this line of code dumps myArr into column A2 of Sheet("A Name of Sheet") and down 12,000 rows.

Of those 12,000 entries, there are 0 to 7 duplicates on any give execution of the code.

How can I remove those duplicates, if they do occur from myArr before dumping into column A?

 myArr = .Range("A2:A" & LRow)

Thanks.
Howard

 

[Rick Rothstein]

All the code preceding this line work just fine, and this line of code dumps myArr into column A2 of Sheet("A Name of Sheet") and down 12,000 rows.

Of those 12,000 entries, there are 0 to 7 duplicates on any give execution of the code.

How can I remove those duplicates, if they do occur from myArr before dumping into column A?

 myArr = .Range("A2:A" & LRow)

What version of Excel are you using?

 

[L. Howard]

Hi Rick,

I have 2010.

 

[Rick Rothstein]

Hi Rick,

I have 2010.

Good, then you do not need to assign it to an array in order to delete duplicates, you can delete them directly on the worksheet. Assuming that dot in front of your Range object in your first message is referring to a With statement whose object references a worksheet, you should be able to use this to remove your duplicates..

.Range("A2:A" & LRow).RemoveDuplicates 1

 

[L. Howard]

Yes, it is within a with statement.

Off to give it a go.

Thanks.

Howard

 

[L. Howard]

Hi Rick,

Here is the entire with statement, it doesn't like the 1 at the end of the RemoveDuplicates, errros out in red. Tried it without the 1 and it did dump into column A but had two duplicates and the line to post the myArr into sheet Publish Data errors out in yellow.

Howard

k = 2
With Sheets("Title Builder")
myArr = .Range("AE2:AO2001")
  For i = LBound(myArr) To UBound(myArr)
    n = 0
    For j = 1 To 11 Step 2
        If Len(Replace(myArr(i, j), " ", "")) > 0 Then
            ReDim Preserve arrOut(n)
            arrOut(n) = myArr(i, j)
            n = n + 1
        End If
    Next
    .Cells(k, 1).Resize(rowsize:=UBound(arrOut) + 1) = _
        WorksheetFunction.Transpose(arrOut)
    k = k + UBound(arrOut) + 1
  Next

    LRow = .Cells(Rows.Count, 1).End(xlUp).Row
    myArr = .Range("A2:A" & LRow).RemoveDuplicates 1
    
End With

Sheets("Publish Data").Range("B2").Resize(rowsize:=UBound(myArr)) = myArr

 

[L. Howard]

Woops, I think I read you reply wrong.

Here is the next thing I did but it did not remove the dupes. (Had three this time, using the Remove Duplicates from the ribbon to check.)

    LRow = .Cells(Rows.Count, 1).End(xlUp).Row
    myArr = .Range("A2:A" & LRow)
                 .Range("A2:A" & LRow).RemoveDuplicates 1

Howard

 

[aminex]

I think you should remove the duplicates before making it an array.

.Range("A2:A" & LRow).RemoveDuplicates Columns:=1, Header:=xlYes

 

[L. Howard]

So I have done this to try to take care of out puts to both Title Builder sheet and Publish Data sheet but the dupes persist. The last three tests all produced 2-3 dupes each code run and the code did not remove them, according to the Data > Remove Duplicates.

I have heard hints that the built in Remove Duplicates can be wrong. I have tried to test that with a countif>1 formula pulled down 12000 rows with a sum at the bottom. Both always agreed on the number of duplicates.

Howard

k = 2
With Sheets("Title Builder")
myArr = .Range("AE2:AO2001")
  For i = LBound(myArr) To UBound(myArr)
    n = 0
    For j = 1 To 11 Step 2
        If Len(Replace(myArr(i, j), " ", "")) > 0 Then
            ReDim Preserve arrOut(n)
            arrOut(n) = myArr(i, j)
            n = n + 1
        End If
    Next
    .Cells(k, 1).Resize(rowsize:=UBound(arrOut) + 1) = _
        WorksheetFunction.Transpose(arrOut)
    k = k + UBound(arrOut) + 1
  Next

    LRow = .Cells(Rows.Count, 1).End(xlUp).Row
    myArr = .Range("A2:A" & LRow)
            .Range("A2:A" & LRow).RemoveDuplicates 1
End With

With Sheets("Publish Data")
  .Range("B2").Resize(rowsize:=UBound(myArr)) = myArr
  .Range("B2:B" & LRow).RemoveDuplicates 1
End With

 

[L. Howard]

Hi aminex,


The array is from every other column AE to AO to be posted in column A.

myArr = .Range("AE2:AO2001")

Howard