I'm currently running code which compares two columns from different worksheets, "Original" and "Updated", and copies the entire row to a new sheet when a match is found. When running through tens of thousands of rows, the code takes too long to execute. I know this can be sped up using arrays, but I'm not too familiar with working with arrays yet. Could someone help me out? Code is below.
Sub ContractCompare()
Dim contract2 as Range, match3 as Range
Dim m as Long
Dim orig_cntr_total as Range
Dim updtws_cntr_total as Range
Application.ScreenUpdating = False
'Sets used range in column 3 as range variable
Sheets("Original").Activate
Range("C2").Select
Range(Selection, Selection.End(xlDown)).Select
Set orig_cntr_total = Selection
'Sets used range in column 3 as range variable
Sheets("UpdatedWS").Activate
Range("C2").Select
Range(Selection, Selection.End(xlDown)).Select
Set updtws_cntr_total = Selection
m = 2
matchcounter = 1 'New sheet will have header, so start at 1
Set contract2 = ActiveWorkbook.Sheets("UpdatedWS").Cells(m, 3)
For Each contract2 In updtws_cntr_total
With orig_cntr_total
Set match3 = .Find(What:=contract2, _
After:=.Cells(.Cells.Count), _
LookIn:=xlValues, _
LookAt:=xlWhole, _
SearchOrder:=xlByRows, _
SearchDirection:=xlNext, _
MatchCase:=False)
If Not match3 Is Nothing Then
matchcounter = matchcounter + 1
match3.EntireRow.Copy _
Sheets("Existing Contracts").Cells(matchcounter, 1)
Else
End If
End With
Next contract2
Application.ScreenUpdating = True
End Sub
Thanks
Bookmarks