CODEWITHSUNDEEP
excelvba
Mash
Mash

Reputation: 11

Check if any rows are duplicate and highlight

I have data in (Sheet4) columns A to I:

I'm trying to compare data for all rows (Only on column A and B) to see if any of the rows is duplicated, if it is: excel should highlight both rows. Example:

A  B  C......I 

s  1  x

s  3  w

e  5  q

s  1  o

Row 1 and 4 should be highlighted as values are the same for column A and B.

I shouldn't modify the sheet (no modification to the columns or rows should be done to the sheet), and the number of rows is not always known (not the same for all files).

Is there an easy way (using macros) to do this???

This is an attempt I have tried, but it is increasing my file to 7MB!!!!! I'm sure there should be an easier way to compare rows for an unknown number of rows and just highlight the dupllicates if they exist:

Public Sub duplicate()
    Dim errorsCount As Integer
    Dim lastrow As Integer
    Dim lastrow10 As Integer
    errorsCount = 0

    lastrow = Sheet4.Cells(Rows.Count, "A").End(xlUp).Row 'is the row number of the last non-blank cell in the specified column
    lastrow10 = lastrow
    Sheet10.Range("B1:B" & lastrow10).Value = Sheet4.Range("A1:A" & lastrow).Value
    Set compareRange = Sheet10.Range(column + "2:" & Sheet10.Range(column + "2").End(xlDown).Address)
    For Each a In Sheet10.Range(column + "2:" & Sheet10.Range(column + "2").End(xlDown).Address)
        c = a.Value
        If c <> Null Or c <> "" Then
            If name = "testing" Then
                       If WorksheetFunction.CountIf(compareRange, c) > 1 Then
                        a.Interior.ColorIndex = 3
                        errorsCount = errorsCount + 1
                       End If
            End If
        End If
    Next a

    If errorsCount > 0 Then
        MsgBox "Found " + CStr(errorsCount) + " errors"
    Else
        MsgBox " No errors found."
    End If
End Sub

Upvotes: 1

Views: 941

Answers (1)

user1543250
user1543250

Reputation: 173

Silly answer to you.

J1 or just duplicate sheet.

J1 =CONCATENATE(A1,"#",B1) > drag down > J:J > conditional format > highlight cells rules > duplicate values.

(* replace the # to any string which you think not possible in the original A:A and B:B.)

I do this all the time.

To collect all duplicates just SORT with color.

Upvotes: 2

Related Questions