Clear Contents on ALL Duplicative Iterations (Not Just One)

[reberryjr]

I have a set of data that can have duplicates in column P. Where a duplicate exists, I want to clear the contents of P:S. The underlying code does that, but it doesn't clear the contents for every duplicate value. I'm not sure how to accomplish the desired result.

Sub test()
Application.DisplayAlerts = False
Application.ScreenUpdating = False

Dim m As Workbook
Dim mS As Worksheet
Dim mSLR As Long
Dim c As Range

Set m = ThisWorkbook
Set mS = m.Sheets("Sheet1")

mSLR = mS.Range("A" & Rows.Count).End(xlUp).Row

For Each c In mS.Range("P2:P" & mSLR)
    With c
        If .Value = c.Offset(-1, 0).Value Then .Resize(, 4).ClearContents
    End With
Next c
 
Application.ScreenUpdating = True
Application.DisplayAlerts = True

End Sub

 

[Joe4]

What cells is the data you have in your screen print in?

 

[BiocideJ]

It looks like your problem is you aren't storing the old value for the row that is ultimately deleted so when you check up a row the 3rd apple is being compared to a blank cell. Minor edit should get you running.

Sub test()
Application.DisplayAlerts = False
Application.ScreenUpdating = False

Dim m As Workbook
Dim mS As Worksheet
Dim mSLR As Long
Dim c As Range
Dim strComp as String

Set m = ThisWorkbook
Set mS = m.Sheets("Sheet1")

mSLR = mS.Range("A" & Rows.Count).End(xlUp).Row

For Each c In mS.Range("P2:P" & mSLR)

    With c
        If strComp <> .Value then
             strComp = .Value 'The first time it finds a value store it
        Else
             If .Value = strComp Then .Resize(, 4).ClearContents  'Subsequent rows will be deleted if repeated
        End If
    End With
Next c
 
Application.ScreenUpdating = True
Application.DisplayAlerts = True

End Sub

To Joe4's point, I am assuming two things. Your cell references are accurate and your data is sorted alphabetically.

 

[Joe4]

It looks like data starts in cell P2. Here is what I came up with:

Sub test()
Application.DisplayAlerts = False
Application.ScreenUpdating = False

Dim m As Workbook
Dim mS As Worksheet
Dim mSLR As Long
Dim r As Long
Dim rng As Range

Set m = ThisWorkbook
Set mS = m.Sheets("Sheet1")

mSLR = mS.Range("A" & Rows.Count).End(xlUp).Row

For r = 2 To mSLR
    Set rng = mS.Range("P2:P" & r)
    If Application.WorksheetFunction.CountIf(rng, mS.Range("P" & r)) > 1 Then
        mS.Range("P" & r).Resize(, 4).ClearContents
    End If
Next r
 
Application.ScreenUpdating = True
Application.DisplayAlerts = True

End Sub

This version does not care how the data is sorted. It will keep the first instance of a value and delete the rest.

 

[reberryjr]

What cells is the data you have in your screen print in?

The column with the duplicates is column P. The cells I need to clear the contents in, is P, Q, R, S of the same row the duplicate is found in.

 

[reberryjr]

It looks like data starts in cell P2. Here is what I came up with:

Sub test()
Application.DisplayAlerts = False
Application.ScreenUpdating = False

Dim m As Workbook
Dim mS As Worksheet
Dim mSLR As Long
Dim r As Long
Dim rng As Range

Set m = ThisWorkbook
Set mS = m.Sheets("Sheet1")

mSLR = mS.Range("A" & Rows.Count).End(xlUp).Row

For r = 2 To mSLR
    Set rng = mS.Range("P2:P" & r)
    If Application.WorksheetFunction.CountIf(rng, mS.Range("P" & r)) > 1 Then
        mS.Range("P" & r).Resize(, 4).ClearContents
    End If
Next r
 
Application.ScreenUpdating = True
Application.DisplayAlerts = True

End Sub

This version does not care how the data is sorted. It will keep the first instance of a value and delete the rest.

@Joe4 this seems to fix the issue. Just so that I understand, we set the range, then run a countif. The CountIF reads the range to determine if there is more than 1 instance of any value. If there is, it clears the contents of the 4 columns, and basically repeats (not the right term) until there is only 1 instance of every value. Correct?

 

[Joe4]

@Joe4 this seems to fix the issue. Just so that I understand, we set the range, then run a countif. The CountIF reads the range to determine if there is more than 1 instance of any value. If there is, it clears the contents of the 4 columns, and basically repeats (not the right term) until there is only 1 instance of every value. Correct?

Yep, you got it for the most part.

Just one point of clarification. It searches for more than one instance of the value in that particular row (not any values).
It loops through each row, and reapeats the process, one at a row, which is how it checks every value.