blank out duplicate cells based on value that is changes in another column

[BORUCH]

hi all

I'm locking for an excel VBA that would blank out cells in Col D (NOT delete entire row) when duplicate data is found but the trick is that it needs to be based on when there is a change of data in column C for ex.

Column C	Column D
ABC	123
ABC	123
ABC	123
DEF	456
DEF	456

So my goal here is to just have the first instances show 123 and the rest blank ETC..

thanks

 

[Crystalyzer]

Sub EraseDupeValuesCol2()
    Dim c As Range
    Dim r As Range
   
    Set r = Range(Cells(1, 3), Cells(Cells(Rows.Count, 3).End(xlUp).Row, 3))
   
    For Each c In r
        If Cells(c.Row + 1, c.Column).Value = c.Value Then
            Cells(c.Row + 1, c.Column + 1).ClearContents
        End If
    Next
   
End Sub

Solutions 20210722.001.xlsm
	C	D
1	ABC	123
2	ABC
3	ABC
4	DEF	456
5	DEF
6	GHI	789
7	GHI
8	GHI
9	GHI
blank out

 

[BORUCH]

Sub EraseDupeValuesCol2()
    Dim c As Range
    Dim r As Range
  
    Set r = Range(Cells(1, 3), Cells(Cells(Rows.Count, 3).End(xlUp).Row, 3))
  
    For Each c In r
        If Cells(c.Row + 1, c.Column).Value = c.Value Then
            Cells(c.Row + 1, c.Column + 1).ClearContents
        End If
    Next
  
End Sub

Solutions 20210722.001.xlsm
	C	D
1	ABC	123
2	ABC
3	ABC
4	DEF	456
5	DEF
6	GHI	789
7	GHI
8	GHI
9	GHI
blank out

Thanks can this be modifed to have column letters like a or c or d the reason being the criteria changes sometimes

thanks

 

[Crystalyzer]

Are you going to select the range that you want to clear values from? If so, will they always be two columns AND will you want to clear the 2nd column based on the changes in the first?

 

[BORUCH]

Are you going to select the range that you want to clear values from? If so, will they always be two columns AND will you want to clear the 2nd column based on the changes in the first?

hi

no i will not be selecting actually my real column that i want modified is column BF and it will be based off column AB

 

[Crystalyzer]

So you want to be able to modify the code more easily to adjust to different source and target columns?

Sub EraseDupeValuesCol2()
    Dim c As Range
    Dim r As Range
    Dim srcCol As String
    Dim trgCol As String
    Dim co As Long
    
    
    srcCol = "AB"
    trgCol = "BF"
    co = Range(trgCol & ":" & trgCol).Column - Range(srcCol & ":" & srcCol).Column
    
    Set r = Range(srcCol & "1:" & srcCol & Cells(Rows.Count, srcCol).End(xlUp).Row)
    For Each c In r
        If Range(c.Address).Offset(1).Value = c.Value Then
            Range(c.Address).Offset(1, co).ClearContents
        End If
    Next
    
End Sub

 

[BORUCH]

So you want to be able to modify the code more easily to adjust to different source and target columns?

Sub EraseDupeValuesCol2()
    Dim c As Range
    Dim r As Range
    Dim srcCol As String
    Dim trgCol As String
    Dim co As Long
   
   
    srcCol = "AB"
    trgCol = "BF"
    co = Range(trgCol & ":" & trgCol).Column - Range(srcCol & ":" & srcCol).Column
   
    Set r = Range(srcCol & "1:" & srcCol & Cells(Rows.Count, srcCol).End(xlUp).Row)
    For Each c In r
        If Range(c.Address).Offset(1).Value = c.Value Then
            Range(c.Address).Offset(1, co).ClearContents
        End If
    Next
   
End Sub

Thank you very much that worked perfectly