Replace duplicate values in Excel column range

[jcw5107]

Hey now..!!
Below is sample code I'm working with.
It replaces duplicate values in a single column only, leaving the 1st row of duplicates.
I'm tryin' to make this work over several columns - columns B thru G to be exact.

Any suggestions or examples..!!
Thanks in advance..!!
jcw5107

Sub ReplaceDups()
Dim RowNdx As Long
Dim ColNum As Integer
ColNum = Selection(1).Column
For RowNdx = Selection(Selection.Cells.Count).Row To _
Selection(1).Row + 1 Step -1
If Cells(RowNdx, ColNum).Value = Cells(RowNdx - 1, ColNum).Value Then
Cells(RowNdx, ColNum).Value = "----"
End If
Next RowNdx
End Sub

 

[SkipVought]

hi,

Sub ReplaceDups()
    Dim RowNdx As Long
    Dim ColNum As Integer
    For ColNum = Selection.Column To Selection.End(xlToRight).Column
        For RowNdx = Selection.End(xlDown).Row To Selection.Row Step -1
            If Cells(RowNdx, ColNum).Value = Cells(RowNdx - 1, ColNum).Value Then
                Cells(RowNdx, ColNum).Value = "----"
            End If
        Next RowNdx
    Next
End Sub

Skip,

_{To be safe on the [red]FOURTH[/red],
Don't take a [red]FIFTH[/red] on the [red]THIRD[/red]
Or you might not come [red]FORTH[/red] on the [red]FIFTH[/red]
[red][highlight blue]FORTH[/highlight][/red][white][highlight red]WITH[/highlight][/white]}

 

[jcw5107]

Skip,

I'm gettin' a run-time error 1004 -- Application defined or object-defined error.

Does your example automaticaly select columns 2 thru 7 then look at each row, and replace the dups except for the 1 row..??

I can't get it to work..

Thanks for your help and fast response time..!!
jcw5107

 

[SkipVought]

On what statement?

"Does your example automaticaly select columns 2 thru 7"

It depends on your SELECTION when you execute the procedure, not the method I would choose.

Skip,

_{To be safe on the [red]FOURTH[/red],
Don't take a [red]FIFTH[/red] on the [red]THIRD[/red]
Or you might not come [red]FORTH[/red] on the [red]FIFTH[/red]
[red][highlight blue]FORTH[/highlight][/red][white][highlight red]WITH[/highlight][/white]}

 

[bdmangum]

jcw5107,

It is not difficult to change the macro so it automatically executes across the first seven columns. Skip was simply modifying your code to work on whatever column is selected.

Are you looking for the code to run without a user selected column? Is the macro ALWAYS going to check the same columns? If so, we can modify the code accordingly.

BD

 

[jcw5107]

bdmangum,

Yes that is exactly what I want to do.
It will always be the same columns (2 thru 7), starting on the 2nd row of the spreadsheet. I would like for it to find the dups between columns 2-7 on each row, and delete or clear out the cell contents of each row, leaving the 1st row of the dups alone...

Hope this makes sense..!!
Thanks,
jcw5107

 

[SkipVought]

Just trying to accomodate your original code. Still don't know what your SELECTION is for the rows?????

Sub ReplaceDups()
    Dim RowNdx As Long
    Dim ColNum As Integer
    For ColNum = 2 To 7
        For RowNdx = Selection.End(xlDown).Row To Selection.Row Step -1
            If Cells(RowNdx, ColNum).Value = Cells(RowNdx - 1, ColNum).Value Then
                Cells(RowNdx, ColNum).Value = "----"
            End If
        Next RowNdx
    Next
End Sub

Skip,

_{To be safe on the [red]FOURTH[/red],
Don't take a [red]FIFTH[/red] on the [red]THIRD[/red]
Or you might not come [red]FORTH[/red] on the [red]FIFTH[/red]
[red][highlight blue]FORTH[/highlight][/red][white][highlight red]WITH[/highlight][/white]}

 

[bdmangum]

jcw5107,

Try this code.

Sub ReplaceDups()
    Dim RowNdx As Long
    Dim ColNum As Integer
    RowNdx = 2  'Sets row value
    For ColNum = 2 To 7 'Loops through columns
        While Sheets(1).Cells(RowNdx, ColNum).Value <> ""   'Loops to the end of the entries
            If Sheets(1).Cells(RowNdx, ColNum).Value = Sheets(1).Cells(RowNdx - 1, ColNum).Value Then
                Sheets(1).Cells(RowNdx, ColNum).Delete Shift:=xlShiftUp     'Deletes value, moves lower values up
                RowNdx = RowNdx - 1 'Counters the cells shifting up
            End If
            RowNdx = RowNdx + 1 'Increments row value
        Wend
    Next
End Sub

Skip,

I think what happened is his original code was pulled from somewhere else and he was trying to modify it to accomidate his project. I think the Selection was left in there, it probably didn't need it. My understanding is there is no need for a selection in his project, due to the area always starting from the same point and covering the same columns.


Hope this helped!

BD