CODEWITHSUNDEEP
excelvba
Lillian
Lillian

Reputation: 51

Delete duplicates array issue

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

Upvotes: 2

Views: 2587

Answers (4)

Charles Gwynn
Charles Gwynn

Reputation: 11

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

Upvotes: 1

peege
peege

Reputation: 2477

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

Upvotes: 0

L42
L42

Reputation: 19727

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

Upvotes: 2

Lillian
Lillian

Reputation: 51

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!

Upvotes: 1

Related Questions