Assign Array to .RemoveDuplicates Columns:=

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

score 4 · Accepted Answer · answered Mar 03 '15 at 15:04

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

Thank you very much for your quick response. It solved my problem perfectly. Also thank you for the formatting hints. Guess I will invest some time to clean my code up. — typie34, Mar 03 '15 at 15:16

score 4 · Answer 2 · answered Jun 22 '16 at 19:32

4

As I just discovered, the Columns:=Evaluate(columnArray) method may not process correctly. Too many rows were removed, not sure why. Try Columns:=(columnArray). This worked for me in Excel 2010.

answered Jun 22 '16 at 19:32

DSH

41
1

With some quick tests, `Evaluate(columnArray)` will be returning only the **first** column number (but, in a format that the function can read), so if you have "1, A" and "1, B", it identifies a duplicate of "1", and ignores that "A" and "B" are different. Just wrapping in the brackets will return *all* of the column values in the 'readable' format – Chronocidal Dec 14 '20 at 16:31

score 3 · Answer 3 · answered Oct 02 '17 at 13:44

Also - with Columns:=(columnArray) make sure that your columnArray is indexed from 0. Otherwise you will get a Run-time error '9': Subscript out of range. So this should look like below:

Sub RemoveDuplicates()

Dim columns As Long
Dim mySheet As Worksheet
Dim myArray()

Set mySheet = ThisWorkbook.Sheets(1) 'To modify

columns = mySheet.UsedRange.columns.Count

ReDim myArray(0 To columns - 1)

For i = 0 To columns - 1
    myArray(i) = i + 1
Next i

mySheet.UsedRange.RemoveDuplicates columns:=(myArray), Header:=xlYes

End Sub

Tested on MS Excel 2013.

score 0 · Answer 4 · answered May 18 '18 at 08:57

Here is another solution

Private Sub removeDuplicates(ByRef arrName As Variant)
  Dim list As New Dictionary
  Dim item As Variant

  For Each item In arrName

    If Not list.Exists(item) Then
        list.Add item, item
    End If
  Next

  arrName = list.Items
End Sub

and then reassign to the range. You can use this also if you want to remove duplicates from an array in vba only.

Assign Array to .RemoveDuplicates Columns:=

4 Answers4

Linked

Related