Assign Array to .RemoveDuplicates Columns:= [duplicate]

Question

I want to delete duplicate rows in the range of columns which hold data.

I first get the last row and the last column used by my data set

lastUsedRowDiff = resultBook.Sheets("Differences").Cells(resultBook.Sheets("Differences").Rows.Count, "A").End(xlUp).Row
lastUsedColumnDiff = resultBook.Sheets("Differences").Cells(6, resultBook.Sheets("Differences").Columns.Count).End(xlToLeft).Column

I tried using the RemoveDuplicate function like this:

resultBook.Sheets("Differences").range(resultBook.Sheets("Differences").Cells(1, 1), resultBook.Sheets("Differences").Cells(lastUsedRowDiff, lastUsedColumnDiff)).RemoveDuplicates Columns:=Array(1, 2, 3, 4, 5, 6, 7, 8, 9, 10, 11, 12, 13, 14, 15, 16, 17, 18, 19, 20, 21, 22, 23, 24, 25, 26, 27, 28, 29, 30, 31, 32, 33, 34, 35, 36, 37, 38, 39, 40, 41, 42, 43, 44), _
Header:=xlNo

It works but I want the number of columns to be dynamic and therefore it is not practical to initialize the array holding the columns that way.

I tried to initialize an array holding the column indices prior to calling the RemoveDuplicates as follows:

ReDim columnArray(1 To lastUsedColumnDiff) As Integer
For p = 1 To lastUsedColumnDiff
    columnArray(p) = p
Next p

When I try to assign the columnArray to Columns:= I get an error.

resultBook.Sheets("Differences").range(resultBook.Sheets("Differences").Cells(1, 1), resultBook.Sheets("Differences").Cells(lastUsedRowDiff, lastUsedColumnDiff)).RemoveDuplicates Columns:=columnArray, _
Header:=xlNo

Here comes the whole code segment:

lastUsedRowDiff = resultBook.Sheets("Differences").Cells(resultBook.Sheets("Differences").Rows.Count, "A").End(xlUp).Row
lastUsedColumnDiff = resultBook.Sheets("Differences").Cells(6, resultBook.Sheets("Differences").Columns.Count).End(xlToLeft).Column

ReDim columnArray(1 To lastUsedColumnDiff) As Integer
For p = 1 To lastUsedColumnDiff
    columnArray(p) = p
Next p

resultBook.Sheets("Differences").range(resultBook.Sheets("Differences").Cells(1, 1), resultBook.Sheets("Differences").Cells(lastUsedRowDiff, lastUsedColumnDiff)).RemoveDuplicates Columns:=columnArray, _
Header:=xlNo

Answer 1

OK so this is interesting... At a glance, I don't understand why it won't accept an array argument for Columns in the RemoveDuplicates method call. Google turned up this answer, which suggests using the VBA Evaluate function on the array, and when I test it, it seems to work as expected.

The trouble seems to be that the RemoveDuplicates method seems to think that cols is a function of some sort. Try using the evaluate() method to get it to recognize it as a variable. This worked for me in Excel 2007 and should work for you. Let me know.

Your code could also benefit from some cleaning up, try this, which uses With blocks to make the code easier to read & modify:

Sub foo()
Dim lastUsedRowDiff As Long
Dim lastUsedColumnDiff As Long
Dim myWorkbook As Workbook
Dim mySheet As Worksheet
Dim columnArray()
Dim p As Long

Set myWorkbook = ThisWorkbook       '## Modify as needed
Set mySheet = ThisWorkbook.Sheets(1) '## Modify as needed

With mySheet
    lastUsedRowDiff = .Cells(.Rows.Count, "A").End(xlUp).Row
    lastUsedColumnDiff = .Cells(6, .Columns.Count).End(xlToLeft).Column
End With

ReDim columnArray(1 To lastUsedColumnDiff)
For p = 1 To lastUsedColumnDiff
    columnArray(p) = p
Next

With mySheet
    .Range(.Cells(1, 1), .Cells(lastUsedRowDiff, lastUsedColumnDiff)).RemoveDuplicates _
        Columns:=Evaluate(columnArray), Header:=xlNo
End With

End Sub