Clear contents in range depending on other range

[123excel]

I have a datasheet with a long list of text from row 2 and below. What I need to do is remove duplicates (clear, not delete), but not on full row, only on range B:D.

The duplicates in range B:D should be cleared if their is other rows with the exact same values in the columns A, C, D and Q.

Example:
A----------B----------C----------D---------Q
One-------Two-------Three------Four------Five
One-------Six-------Three------Four------Five

In the example above, B:D should ClearContents because A,C,D and Q is the same.

Right now I have only come up with this, but it does not do what I want because it checks if A has a duplicate, if C has a duplicate and so on.. It needs to make it so that the A,C,D and Q is counting as one duplicated value together.

Any great ideas?

Sub remove_dup()


Dim X As Long




For X = 2 To Range("C" & Rows.Count).End(xlUp).Row
    If Application.WorksheetFunction.CountIf(Range("A2:A" & X), Range("A" & X).Text) > 1 And Application.WorksheetFunction.CountIf(Range("C2:C" & X), Range("C" & X).Text) > 1 And Application.WorksheetFunction.CountIf(Range("D2:D" & X), Range("D" & X).Text) > 1 And Application.WorksheetFunction.CountIf(Range("Q2:Q" & X), Range("Q" & X).Text) > 1 Then
        Intersect(Range("B:D"), Rows(X)).ClearContents
    End If
Next


End Sub

 

[steve the fish]

Do you want to clear all of the duplicated entries or just the duplicates (ie leave the first one)?

 

[123excel]

I just need to clear the duplicates, there should be one left

 

[steve the fish]

This is how you could tweak what you wrote:

With Sheets("Sheet1") 'change as appropriate
    lr = .Range("C" & .Rows.Count).End(xlUp).Row
    If lr > 2 Then
        For i = 3 To lr
            If Application.WorksheetFunction.CountIfs(.Range("A2:A" & i), .Range("A" & i).Value, .Range("C2:C" & i), .Range("C" & i).Value, .Range("D2:D" & i), .Range("D" & i).Value, .Range("Q2:Q" & i), Range("Q" & i).Value) > 1 Then
                .Range(.Cells(i, "B"), .Cells(i, "D")).ClearContents
            End If
        Next
    End If
End With

 

[123excel]

Worked perfect! Thanks alot!