niebyl2002
02-28-2012, 04:29 PM
Hello,
I'm trying to optimize my code that compares two different worksheet tabs. Both worksheets have same report, just different days. BTW rows order will be different. I dedicated column A to create unique identifier which will be used for vlookup to find the same row on a new worksheet and then it will compare whole row.
Code needs to go thru around 10,000 rows times 20 columns. If I run a code with that range excel stops responding and quits.
Everything is working if I keep rows below 1000.
Please see my code below and advise what to do?
Sub SheetCompare()
Start = Timer
Sheets("PO Download OLD").Select
Range("A8:BL7000").Interior.ColorIndex = 3
Dim myRow_OLD As Integer
Dim myRow_NEW As
Integer
Dim myCol As Integer
Dim Col1 As
Integer
Col1 = 1
Application.ScreenUpdating = False
For myRow_NEW = 8 To 500
For myRow_OLD = 8 To 500
If Sheets("PO Download NEW").Cells(myRow_NEW, Col1).Value _
= Sheets("PO Download OLD").Cells(myRow_OLD, Col1).Value Then
For myCol = 1 To 64
If Sheets("PO Download NEW").Cells(myRow_NEW, myCol).Value _
= Sheets("PO Download OLD").Cells(myRow_OLD, myCol).Value Then
Sheets("PO Download OLD").Cells(myRow_OLD, myCol).Interior.ColorIndex = 0
End If
Next myCol
End If
Next
myRow_OLD
Next myRow_NEW
Application.ScreenUpdating = True
MsgBox "Report run for " & Timer - Start & " Seconds"
End Sub
Thank you
I'm trying to optimize my code that compares two different worksheet tabs. Both worksheets have same report, just different days. BTW rows order will be different. I dedicated column A to create unique identifier which will be used for vlookup to find the same row on a new worksheet and then it will compare whole row.
Code needs to go thru around 10,000 rows times 20 columns. If I run a code with that range excel stops responding and quits.
Everything is working if I keep rows below 1000.
Please see my code below and advise what to do?
Sub SheetCompare()
Start = Timer
Sheets("PO Download OLD").Select
Range("A8:BL7000").Interior.ColorIndex = 3
Dim myRow_OLD As Integer
Dim myRow_NEW As
Integer
Dim myCol As Integer
Dim Col1 As
Integer
Col1 = 1
Application.ScreenUpdating = False
For myRow_NEW = 8 To 500
For myRow_OLD = 8 To 500
If Sheets("PO Download NEW").Cells(myRow_NEW, Col1).Value _
= Sheets("PO Download OLD").Cells(myRow_OLD, Col1).Value Then
For myCol = 1 To 64
If Sheets("PO Download NEW").Cells(myRow_NEW, myCol).Value _
= Sheets("PO Download OLD").Cells(myRow_OLD, myCol).Value Then
Sheets("PO Download OLD").Cells(myRow_OLD, myCol).Interior.ColorIndex = 0
End If
Next myCol
End If
Next
myRow_OLD
Next myRow_NEW
Application.ScreenUpdating = True
MsgBox "Report run for " & Timer - Start & " Seconds"
End Sub
Thank you