Thread: Delete duplicates

Hi, the code in the codebox under here, which I wrote, is supposed to delete all duplicate records in the column. Which column to uniquify is stated in cell Skin!B17. I searched the internet round, and there are different solutions everywhere. I have been unable though to find the right one - so I thought, why not ask here. If you have a solution, I'd be happy to hear it.

Code:

Sub Remove_Duplicates_In_A_Range()
    
    Dim x               As Long
    Dim LastRow         As Long
    
Sheets("VLB").Select

  LastRow = Range("A65536").End(xlUp).Row
    For x = LastRow To 1 Step -1
        If Application.WorksheetFunction.CountIf(Sheets("Skin").Range(B17 & "1:" & B17 & x), Sheets("Skin").Range(B17 & x).Text) > 1 Then
            Sheets("Skin").Range(B17 & x).EntireRow.Delete
        End If
    Next x
     
End Sub

Try this, your range definition looks wrong

Code:

    Dim x      As Long
    Dim Col    As Long
    Dim LastRow As Long

    With Sheets("VLB")
        Col = .Cells(17, 2).Value
        LastRow = .Cells(.rows.count).End(xlUp).Row
End with

With Sheets("Skin")
        For x = LastRow To 1 Step -1
            If Application.WorksheetFunction.CountIf(.Cells(1, _
                             Col), .Cells(LastRow, Col)).Value) > 1 Then
                .Cells(x,Col).EntireRow.Delete
            End If
        Next x

Try this (untested):

Code:

Sub RemoveDups()
    Dim iRow    As Long
    Dim iCol    As Long
    Dim iRowEnd As Long

    With Sheets("VLB")
        iCol = .Range("B17").Value
        iRowEnd = .Cells(.Rows.Count).End(xlUp).Row
    End With

    With Sheets("Skin").Columns(iCol)
        For iRow = iRowEnd To 2 Step -1
            If WorksheetFunction.CountIf(Range(.Cells(1), .Cells(iRow - 1)), .Cells(iRow).Value) > 0 Then
                .Cells(iRow).EntireRow.Delete
            End If
        Next iRow
    End With
End Sub

Code:

Sub RemoveDups()
    Dim iRow    As Long
    Dim iCol    As Long
    Dim iRowEnd As Long

    With Sheets("VLB")
        iCol = .Range("B17").Value
        iRowEnd = .Cells(.Rows.Count).End(xlUp).Row
    End With

    With Sheets("Skin").Columns(iCol)
        For iRow = iRowEnd To 2 Step -1
            If WorksheetFunction.CountIf(Range(.Cells(1), .Cells(iRow - 1)), .Cells(iRow).Value) > 0 Then
                .Cells(iRow).EntireRow.Delete
            End If
        Next iRow
    End With
End Sub

It still gives me the 1004 error (on the red line). In cell B17 is a formula, which calculates the column character. I hope that is not a problem,

If B17 contains a string (e.g., "A"), then the column variable has to be either a string or a Variant (which will accommodate a number or a string):

Code:

Sub RemoveDups()
    Dim iRow    As Long
    Dim vCol    As Variant
    Dim iRowEnd As Long

    With Sheets("VLB")
        vCol = .Range("B17").Value
        iRowEnd = .Cells(.Rows.Count).End(xlUp).Row
    End With

    With Worksheets("Skin").Columns(vCol)
        For iRow = iRowEnd To 2 Step -1
            If WorksheetFunction.CountIf(Range(.Cells(1), .Cells(iRow - 1)), .Cells(iRow).Value) > 0 Then
                .Cells(iRow).EntireRow.Delete
            End If
        Next iRow
    End With
End Sub

Actually, B17 contains this formula:

Code:

=SUBSTITUTE(ADDRESS(1;MATCH(A17;XML!1:1;0);4);1;"")

"XML" is a sheet name and A17 is the string to match.

It still gives a 1004 on the same line as before

What result is returned by the formula?

Post a workbook.

VLB!B17 is empty.

Okay, that's not right. It should be on the XML sheet, B17. The sheets got mixed up (purple). The semi-correct code:

Code:

Sub Remove_Duplicates_In_A_Range()
    
    Dim x      As Long
    Dim Col    As Long
    Dim LastRow As Long

'Define ranges
With Sheets("Skin")
        Col = .Cells(17, 2).Value
        LastRow = .Cells(.Rows.Count).End(xlUp).Row
End With

'Execute removal
With Sheets("VLB")
        For x = LastRow To 1 Step -1
            If Application.WorksheetFunction.CountIf(.Cells(1, Col), .Cells(LastRow, Col)).Value > 1 Then
                .Cells(x, Col).EntireRow.Delete
            End If
        Next x
End With
End Sub

Now it gives me an invalid qualifier (red)... I guess I'd buy the book VBA for Dummies or something

How about posting a correct example, mon frere?