Delete duplicates array issue

Question

I got this code from this website but I'm having issue formatting it to how I need to use it. The code was initially only for Columns A and B, but I need it to work for Columns A:F, I fixed the top portion to refer to my Columns of A:F but I'm having issues with the Array, I'm new to VBA so I'm not 100% sure on how that even works, I just know I'm getting an error on that line. Here's my code.

Sub DeleteRows()    
    With ActiveWorkbook.Worksheets("MC RRRs")
        Set Rng = Range("A:F").End(xlDown)
        Rng.RemoveDuplicates Columns:=Array(1, 2), Header:=xlYes
    End With   
End Sub

What is the error? Have you tried recording a macro and comparing the code it generates to this code? — peege, Dec 03 '14 at 00:41
That didn't work. I'm getting a run-time error '5': Invalid procedure call or argument. — Lillian, Dec 03 '14 at 00:58
I posted an answer for you anyway that you might pick up a few ideas for how to make the macro you recorded more dynamic and adaptable. :) — peege, Dec 03 '14 at 01:46

L42 · Answer 1 · 2014-12-03T01:31:25.910

To answer this question, your problem is with this line:

Set Rng = Range("A:F").End(xlDown)

End method/property should not be used since it will make you work on the last cell in Range("A:F").
That means you only have one(1) cell to work on but your next line:

Rng.RemoveDuplicates Columns:=Array(1, 2), Header:=xlYes

tries to remove duplicates in two(2) columns (or at least 2 cells).
Also if only one(1) cell is selected, setting Header argument to xlYes will also generate error.
Also, since you are using With Clause, precede Range by a dot.
Something like:

Sub DeleteRows()
    Dim Rng As Range
    With ActiveWorkbook.Worksheets("MC RRRs")
        Set Rng = .Range("A:F")
        Rng.RemoveDuplicates Columns:=Array(1, 2, 3, 4, 5, 6), Header:=xlYes
    End With
End Sub

score 1 · Answer 2 · edited Dec 03 '14 at 01:19

1

Sub DeleteRows()
    Columns("A:F").Select
    ActiveSheet.Range("A:F").RemoveDuplicates _
        Columns:=Array(1, 2, 3, 4, 5, 6), Header:=xlYes
End Sub

I used the recorder button and it worked. Thanks for the advice!

edited Dec 03 '14 at 01:19

L42

19,427
11
44
68

answered Dec 03 '14 at 01:10

Lillian

51
2
9

2

Good job for figuring it out. But I posted something that explains why your first code didn't work. Also [check this out](http://stackoverflow.com/questions/10714251/how-to-avoid-using-select-in-excel-vba-macros) to help you improve coding. – L42 Dec 03 '14 at 01:42
Excellent link. I'm going to refer to that every few hours, I'm thinking. – peege Dec 03 '14 at 01:45
Not a problem. Also you can accept your own answer or any other answer that you think best solved your problem. [Check this out on how to accept answers.](http://stackoverflow.com/help/someone-answers) – L42 Dec 03 '14 at 22:09

score 1 · Answer 3 · answered Mar 13 '16 at 20:48

After plenty of toiling over this array issue, I finally produced some working code as well. I hope this helps someone in need. The "-5" can be adjusted to suit your needs. I didn't need to look at each column in my specific instance, but if you do, you can increase this to "-1". One key to success was the parentheses around "arrColstoCheck" when using the RemoveDuplicates command.

Sub RemoveDuplicates()

Dim rngDupes As Range
Dim lngCols As Long
Dim lngRows As Long
Dim i As Long
Dim strCols As String
Dim arrColstoCheck() As Variant
Dim wsComData1 as Worksheet

Set wsComData1 = Application.ActiveSheet

With wsComData1

    .Activate

    'Determine number of columns and rows in worksheet
    lngCols = .Cells(1, Columns.Count).End(xlToLeft).Column
    lngRows = .Cells(Rows.Count, 1).End(xlUp).Row

    ReDim arrColstoCheck(0 To lngCols - 5)
        'Fill array with column numbers
        For i = 0 To lngCols - 5
            arrColstoCheck(i) = i + 1
        Next i

    'Convert lngCols to Character for later use
    strCols = Chr(lngCols + 64)
    Set rngDupes = .Range("A1:" & strCols & lngRows)

        rngDupes.RemoveDuplicates Columns:=(arrColstoCheck), Header:=xlNo

    End With

End Sub

score 0 · Answer 4 · answered Dec 03 '14 at 01:39

Tested: This will go through all the columns though. If you want to set the limit, just insert a max instead of all columns.

Sub RemoveDuplicates()

Dim lastRow As Long
Dim tempLast As Long
Dim lastCol As Long
Dim colLet As String
Dim iCol As Integer  'because ConvertToLetter uses Integers

lastCol = Sheets("Sheet1").Cells(1, Columns.Count).End(xlToLeft).Column
lastRow = 0

'Get the true last row no matter what column it is in.  Loop through each and check.
For iCol = 1 To lastCol
    colLet = ConvertToLetter(iCol)
    lastRow = Sheets("Sheet1").Range(colLet & "2").End(xlDown).Row
    ActiveSheet.Range(colLet & "1:" & colLet & lastRow).RemoveDuplicates Columns:=1, Header:=xlYes
Next iCol

End Sub

Function ConvertToLetter(iCol As Integer) As String
'FROM http://support.microsoft.com/kb/833402
   Dim iAlpha As Integer
   Dim iRemainder As Integer
   iAlpha = Int(iCol / 27)
   iRemainder = iCol - (iAlpha * 26)
   If iAlpha > 0 Then
      ConvertToLetter = Chr(iAlpha + 64)
   End If
   If iRemainder > 0 Then
      ConvertToLetter = ConvertToLetter & Chr(iRemainder + 64)
   End If
End Function

Delete duplicates array issue

4 Answers4