remove duplicate if cell appears twice macro

[daveyc18]

the removed duplicates function because other columns are different

let's assume the duplicated value appears in column A

i'd like the macro to delete only ONE of the duplicates

...

i have a helper column in Y that does a countif and this code

    finalrow = Cells(Rows.Count, "A").End(xlUp).Row


For i = finalrow To 2 Step -1

If Range("y" & i) > 1 Then
Range("y" & i).EntireRow.Delete
Exit Sub
End If
Next i

but the issue with this code is that it would stop once it finds the any row with 2 and there could be multiple rows with duplicate values in column A (this code only works if there's only two rows of data with duplicates, but, not, say, 4)

 

[dermie_72]

OK, so the duplicate range is column D, so if you change the countif range to D:D it will work for the whole set of data

For i = finalrow To 2 Step -1
If application.worksheetfunction.countif(Range("D:D"), Range("D" & i) > 1 Then
    Range("D" & i).EntireRow.Delete
End If

 

[dermie_72]

If the column that has the duplicates is in column D then the code would be:

For i = finalrow To 2 Step -1
If application.worksheetfunction.countif(Range("D:D"), Range("D" & i) > 1 Then
    Range("D" & i).EntireRow.Delete
End If

If you want it to be versatile you can change the column as a variable like this:

DC = "D"
For i = finalrow To 2 Step -1
If application.worksheetfunction.countif(Range(DC & ":" & DC), Range(DC & i) > 1 Then
    Range(DC & i).EntireRow.Delete
End If

Then just change the DC variable depending on the column you're checking

 

[daveyc18]

If the column that has the duplicates is in column D then the code would be:

For i = finalrow To 2 Step -1
If application.worksheetfunction.countif(Range("D:D"), Range("D" & i) > 1 Then
    Range("D" & i).EntireRow.Delete
End If

If you want it to be versatile you can change the column as a variable like this:

DC = "D"
For i = finalrow To 2 Step -1
If application.worksheetfunction.countif(Range(DC & ":" & DC), Range(DC & i) > 1 Then
    Range(DC & i).EntireRow.Delete
End If

Then just change the DC variable depending on the column you're checking

tks

 

[Fluff]

@daveyc18 I have unchecked post#12 as the solution simply because it cannot work.

 

[dermie_72]

@daveyc18 I have unchecked post#12 as the solution simply because it cannot work.

You're right, I missed a bracket. here is the working code

Finalrow = Cells(Rows.Count, "A").End(xlUp).Row
DC = "D"
For i = Finalrow To 2 Step -1
    If Application.WorksheetFunction.CountIf(Range(DC & ":" & DC), Range(DC & i)) > 1 Then
        Range(DC & i).EntireRow.Delete
    End If
Next i
End Sub

 

[daveyc18]

@daveyc18 I have unchecked post#12 as the solution simply because it cannot work.

tks ...I figured out why but all good ..tks everyone