Find Duplicates and Highlight Row in Red

[trevor2524]

Hello Everybody,

Let me explain exactly what I'm trying to figure out. I am looking in every row starting in Columns P, Q and R. It will go to the next row below the first and see if those columns have the same exact value. If it doesn't it goes onto the next row. If it turns out that row 1 matches row 2 then the program will then check to see if columns A-O in row 2 are all empty. If they are all empty then the entire row of Row 1 will be highlighted Red. If A-O are not empty it will skip over that row and go back to check columns P, Q, and R. This program would run until it gets to four empty rows in a row and then it will stop. I also forgot to add that after it makes Row 1 red it would then need to delete Row 2 completely. And then restart the program and move onto the next row. I know it might sound confusing, The program is basically combining two worksheets and I'm seeing which files need to be deleted by marking it red. Thanks for your help.

 

[JLGWhiz]

Make a copy of your original file to test this on.

I am assuming that since you want to delete duplicate rows, that cells A:O in each row do not contain formulas, because if they do then this code fails.

Sub compNdel()
Dim sh As Worksheet, lr As Long
Set sh = Sheets(1) 'Edit sheet name
lr = sh.Cells(Rows.Count, "P").End(xlUp).Row
    For i = lr To 3 Step -1
        With sh
            If .Cells(i, 16) = .Cells(i - 1, 16) And .Cells(i, 17) = .Cells(i - 1, 17) _
            And .Cells(i, 18) = .Cells(i - 1, 18) Then
                If Application.CountA(.Cells(i, 1).Resize(1, 15)) = 0 Then 'This line assumes no formulas
                    .Rows(i - 1).Interior.ColorIndex = 3
                    .Rows(i).Delete
                End If
            End If
        End With
    Next
End Sub

 

[trevor2524]

Thanks for your response. The only question I have is how come it starts from the bottom and works its way to the top. It's just confusing me and I just want to me sure it wont give me a problem doing it that way instead of going from the top to the bottom.

 

[JLGWhiz]

Thanks for your response. The only question I have is how come it starts from the bottom and works its way to the top. It's just confusing me and I just want to me sure it wont give me a problem doing it that way instead oYou would have more problems going from the top down than from the bottom up. It only requires one pass when going from the bottom up because it will not skip any rows as a result of a deletion. However, from top down, it does skip rows because the For next loop advances after the rows are deleted causing it to miss the row that shifted up. The code is written so that the second row of any duplicates will still be the deleted row.f going from the top to the bottom.

You would have more problems going from the top down than from the bottom up. It only requires one pass when going from the bottom up because it will not skip any rows as a result of a deletion. However, from top down, it does skip rows because the For next loop advances after the rows are deleted causing it to miss the row that shifted up. The code is written so that the second row of any duplicates will still be the deleted row.

 

[ExcelIsEasy]

You can also find duplicates and highlight cell. You can do this even without vba. Here is the link: Excel Find Duplicates