Excel VBA - how to select a range of cells containing dups and clearing (not deleting) 2

[eshie003]

I have a worksheet with duplicate data in columns A:K, however I have additional data in the colums L:Z. I want to loop through the range and if there are duplicates (column A would be the best to evaluate dups on) then I want to keep the first record and clear the remaining dup selection.
Thanks in advance for any help!

Example (original data) columns A:K
ROWID Name DOB Member ID Order Date Process Date Fulfill Date Part Billed Paid Balance
1 John Smith 1/1/2011 2111 2/1/2013 2/1/2013 2/3/2013 Tire 150.00 100.00 50.00
1 John Smith 1/1/2011 2111 2/1/2013 2/1/2013 2/3/2013 Tire 150.00 100.00 50.00
2 Mary Rose 2/1/2009 3222 3/1/2013 3/1/2013 3/3/2013 Wheel 200.00 125.00 75.00
2 Mary Rose 2/1/2009 3222 3/1/2013 3/1/2013 3/3/2013 Wheel 200.00 125.00 75.00

Example After:
ROWID Name DOB Member ID Order Date Process Date Fulfill Date Part Billed Paid Balance
1 John Smith 1/1/2011 2111 2/1/2013 2/1/2013 2/3/2013 Tire 150.00 100.00 50.00

2 Mary Rose 2/1/2009 3222 3/1/2013 3/1/2013 3/3/2013 Wheel 200.00 125.00 75.00

 

[mintjulep]

Is this a one-time task?

Could be done using native functions in a few moments. No need for VBA.

 

[eshie003]

No, this is not a one-time task. I would like to develop code and modify it as needed for other spreadsheets.

 

[SkipVought]

What is the business case for not just eliminating the rows?

Skip,
[sub]
Just traded in my old subtlety...
for a NUANCE![/sub]

 

[eshie003]

I have data in the adjoining columns that correlate with the data in A:K. This is the output from a 1 to many relationship table and I need to keep all the data in L:Z.

 

[zelgar]

This might not be the best method, but it works. Note, if you want more comparisons (e.g., DOB), just add them around the name_i & name_j lines (e.g., DOB_i=cells(irow,3).value, DOB_j=cells(jrow,3); and add in the If statement AND DOB_i=DOB_j):

Sub clear_dups()
'
'
'
Dim irow As Integer
Dim jrow As Integer
Dim name_1 As String
Dim name_j As String

' Start irow = to 1st value of data
irow = 2
Do Until IsEmpty(Cells(irow, 2))
    jrow = irow + 1
    name_i = Cells(irow, 2).Value
    If name_1 <> "To be deleted" Then
        Do Until IsEmpty(Cells(jrow, 2))
            name_j = Cells(jrow, 2).Value
            If name_i = name_j Then Cells(jrow, 2) = "To be deleted"
            jrow = jrow + 1
        Loop
    End If
irow = irow + 1
Loop
' this goes back and clears the "To be deleted" rows
irow = 2
Do Until IsEmpty(Cells(irow, 2))
    name_i = Cells(irow, 2).Value
    If name_i = "To be deleted" Then Rows(irow).Clear
    irow = irow + 1
Loop

'
End Sub

 

[zelgar]

You'll need to adjsut my code, since the above will clear out the entire rows. If you only want the data in columns A to K cleared, make the following change to the above macro:
If name_i = "To be deleted" Then Rows(irow).Clear
to
If name_i = "To be deleted" Then Range(Cells(irow,1), Cells(irow, 11)).Clear

 

[eshie003]

Hi,

I modified as I needed to use the ROWID, not Name as identifier. This works great, however, it is deleting the entire row of dups and I only need to delete the dups in A:K while keeping ALL the data intact in L:Z

 

[zelgar]

See my post at 15:00

 

[eshie003]

This worked! Thanks Zelgar...you're the best!

 

[SkipVought]

I have data in the adjoining columns that correlate with the data in A:K. This is the output from a 1 to many relationship table and I need to keep all the data in L:Z.

Well it would seem that the DATA that you are deleting IS applicable to ALL the rows. By deleting the data, you have destroyed the usefulness of the table for both you and anyone else who might want to make use of this table!

A better alternative is to use Excel's Conditional Formatting feature to make the repeating data INVISIBLE by making the font color the same as the cell interior.

Skip,
[sub]
Just traded in my old subtlety...
for a NUANCE![/sub]

 

[zelgar]

If you want to modify the macro to only make the data invisible as Skip suggest (and is better since you're not deleting any data), make the following changes:
1. add a Dim c as Variant
2. Change the following If statement
If name_i = "To be deleted" Then Rows(irow).Clear

to
If name_i = "To be deleted" Then
c = Cells(irow, 1).Interior.Color
Range(Cells(irow, 1), Cells(irow, 11)).Font.Color = c
End If

 

[SkipVought]

I'd sugest

If name_i = "To be deleted" Then
  c = Cells(irow, 1).Interior.Color
else
  c = 1
End If
Range(Cells(irow, 1), Cells(irow, 11)).Font.Color = c

Skip,
[sub]
Just traded in my old subtlety...
for a NUANCE![/sub]

 

[zelgar]

Skip, I don't understand the need for your change. My way would only change the rows that are "To be deleted", whereas your way would affect every row of data. The only change I would suggest would be if the user is only using one type of background for all the cells, then the c=Cells(irow,1).Interior.Color could be moved outside of the DO UNTIL loops right after irow=2

 

[SkipVought]

Actually, I would not do it that way at all. I'd use the Conditional Formatting feature as suggested on 5 Mar 13 17:50, and not VBA code.

If I were using VBA I'd first assign all the font to automatic and then change each row as your code was constructed. The point was implicit that any change in the data structure may require assigning font color to display the first row in a group. So that code is a quick way to effect that change.

Skip,
[sub]
Just traded in my old subtlety...
for a NUANCE![/sub]