Clear contents for duplicates in range

[123excel]

Hi, I am trying to clear the contents (not deleting) for duplicates of the Range A36:G36 and downwards (only the duplicated values should be deleted, the original should not be cleared).

Right now I have some code that do almost what I am after. The problem is that it is clearing the full row, not just range A:G.

How can I make it work with just the Range and not the full row?


Here is the code I got:

Sub RemoveDuplications()
Dim X As Long
For X = 1 To Range("A" & Rows.Count).End(xlUp).Row
    If Application.WorksheetFunction.CountIf(Range("A36:A" & X), Range("A" & X).Text) > 1 Then Rows(X).ClearContents
Next
End Sub

 

[Misca]

Try something like:

Sub RemoveDuplications()

Dim X As Long


For X = 37 To Range("A" & Rows.Count).End(xlUp).Row
    If Application.WorksheetFunction.CountIf(Range("A36:A" & X), Range("A" & X).Text) > 1 Then
        Intersect(Range("A:G"), Rows(X)).ClearContents
    End If
Next


End Sub

Your code was missing the Intersect-part. Also, I understood that you wanted to look duplicates from A36 down so I started my X from 37.

 

[Fluff]

Another option

Sub I23excel()
   Dim cl As Range
   
   With CreateObject("scripting.dictionary")
      .CompareMode = 1
      For Each cl In Range("A36", Range("A" & Rows.Count).End(xlUp))
         If Not .Exists(cl.Value) Then .Add cl.Value, Nothing Else cl.Resize(, 7).ClearContents
      Next cl
   End With
End Sub

 

[123excel]

Thank you both Misca and Fluff!

Miscas version worked great so I am going to stick to that.

Thanks a lot for both your help! I really appreciate it.

 

[Fluff]

Glad we could help & thanks for the feedback