Make code faster/better best practices

hi Shg, below is the code i came up with based on your helpful inputs. I learned alot.

Sub compare_lists(droparray() As String, addarray() As String, Currentlist As Range, Newlist As Range)

Dim x As Long
Dim mtch As Boolean
Dim array_cntr As Long
Dim cntr As Long


'droparray
array_cntr = 1
For x = 1 To Currentlist.Count
    cntr = 1
    mtch = False
    Do
        If Currentlist.Cells(x, 1) = Newlist.Cells(cntr, 1) Then
            mtch = True
        Else: cntr = cntr + 1
        End If
        If mtch Or (cntr > Newlist.Count) Then Exit Do
    Loop
    If Not mtch Then
    ReDim Preserve droparray(array_cntr)
    droparray(array_cntr) = Currentlist.Cells(x, 1)
    array_cntr = array_cntr + 1
    End If
Next x

'addarray
array_cntr = 1
For x = 1 To Newlist.Count
    cntr = 1
    mtch = False
    Do
        If Newlist.Cells(x, 1) = Currentlist.Cells(cntr, 1) Then
            mtch = True
        Else: cntr = cntr + 1
        End If
        If mtch Or (cntr > Currentlist.Count) Then Exit Do
    Loop
    If Not mtch Then
    ReDim Preserve addarray(array_cntr)
    addarray(array_cntr) = Newlist.Cells(x, 1)
    array_cntr = array_cntr + 1
    End If
Next x

'for debug
'For x = 1 To UBound(droparray())
'Workbooks("working_file.xlsm").Worksheets("compare_test").Range("j" & x + 1) = droparray(x)
'Next x

'for debug
'For x = 1 To UBound(addarray())
'Workbooks("working_file.xlsm").Worksheets("compare_test").Range("k" & x + 1) = addarray(x)
'Next x


End Sub

Hi Shg,

Sure i can do that. How do i calculate the time for a procedure to run? Do you know an easy way?

David

Hi Shg,

I ran two tests. One using your program and one using mine. I used the same names that were in your original spread sheet. I think the first list had ~1688 entries and the 2nd has ~ 1679 entries.

Yours took ~ 0.281 sec
Mine took ~ 39sec

Thats a BIGGGGGGGGGGGGG difference.

Shg,

Yours is definitely faster. Thanks.

Yes i will mark as solved.