Adjust RemoveDuplicates code

[Wil Moosa]

I use the following code in a few workbooks:

ActiveSheet.Range("A2:Z100").RemoveDuplicates Columns:=Array(3, 4, 5, 6, 7, 8, 9, 10, 11, 12, 13, 14, 15, 17)

As I don’t really fully onderstand the array-thing I haven’t been able to adjust the code to my new needs.

The situation is now that I have a range of A1:AK100 where A1:AK1 = the xlheader. I want to remove every duplicate row - including A:C - based on the data in the columns D:AK. I gave it a try as follows but it did not work as I intended. Any suggestions?

CODE=vba]ActiveSheet.Range("A2:AK100").RemoveDuplicates Columns:=Array(4, 5, 6, 7, 8, 9, 10, 11, 12, 13, 14, 15, 17)[/CODE]

 

[Domenic]

Maybe something like this...

Sub test()

    Dim dataRange As Range
    Set dataRange = Range("A2:AK100")
   
    Dim duplicateColumns As Range
    Set duplicateColumns = Range("D2:AK100")
   
    ReDim arr(0 To duplicateColumns.Columns.Count - 1) As Variant
   
    Dim columnIndex As Long
    columnIndex = 4
   
    Dim arrayIndex As Long
    For arrayIndex = LBound(arr) To UBound(arr)
        arr(arrayIndex) = columnIndex
        columnIndex = columnIndex + 1
    Next arrayIndex
   
    dataRange.RemoveDuplicates Columns:=(arr), Header:=xlNo 'brackets around arr are required
   
End Sub

Hope this helps!

 

[Wil Moosa]

Got time to check. Works great, does exactly what I asked for... and very smooth.

Thank you Domenic. Patrick, you're fired!

 

[Domenic]

Got time to check. Works great, does exactly what I asked for... and very smooth.

That's great, glad I could help.

Thank you Domenic. Patrick, you're fired!

That's funny.