Macro that compares whole rows in two sheets

Question

I am looking for a macro that can do the following:

1) See if any rows from MECH_COMBINED sheet exist with the exact same info in the COMPONENTS sheet. (There are around 7000 rows in MECH_COMBINED, 20000 in COMPONENTS, each sheet has columns that go up to BI same column names)

2) If rows in MECH_COMBINED exist then highlight the whole row in the COMPONENTS sheet and create a third sheet with the different rows (if thats not possible then the third sheet could have the same highlighted rows

I hope this is a possible macro? the one I am using now runs way too slowly and ends up freezing excel.

Sub Test() 
Application.ScreenUpdating = False 
Dim bottomA1 As Integer bottomA1 = Sheets("Sheet1").Range("A" & Rows.Count).End(xlUp).Row Dim c As Range 
Dim bottomA2 As Integer bottomA2 = Sheets("Sheet2").Range("A" & Rows.Count).End(xlUp).Row Dim x As Integer For Each c In Sheets("Sheet2").Range("A1:A" & bottomA2) 
For x = bottomA1 To 2 Step -1 If Cells(x, 1) = c Then Cells(x, 1).EntireRow.Interior.Color = 255 End If Next x Next c Application.ScreenUpdating = True 
End Sub

Thanks in advance :)

Please put your code in the question, where it can be formatted for legibility. — David Zemens

artis_meditari artis_meditari · Accepted Answer · 2013-05-29T16:28:28

The highlight differs between Excel Versions.  Record a macro that highlights then modify 
and insert it.  Here is a rough macro hopely it is what you want.

Sub macro1()
n = 0
For i = 1 To Sheets("MECH_COMBINED").Cells(Rows.Count, "A").End(xlUp).Row
    For j = 1 To Sheets("COMPONENTS").Cells(Rows.Count, "A").End(xlUp).Row
        For k = 1 To 51 'A to BI
            If Sheets("MECH_COMBINED").Cells(i, k) = Sheets("COMPONENTS").Cells(j, k) Then
                If notequal = 0 Then
                    If k = 51 Then

                        'Highlight Row in Sheets("COMPONENTS")

                        'copy complete row
                        n = n + 1
                        For m = 1 To 51
                            Sheets("Sheet3").Cells(n, m) = Sheets("MECH_COMBINED").Cells(i, m)
                        Next

                        'highlight complete row in Sheets("Sheet3")

                    End If
                End If
            Else
                notequal = 1
            End If
        Next k
        notequal = 0
    Next j
Next i
End Sub

Macro that compares whole rows in two sheets

1 Answers