Make code faster/better best practices

[welchs101]

Hi all: I have the following section of code and i am trying to make it faster. Currently when this procedure runs the screen "changes" durings its execution. Now i know i can "turn off screen updating" and this will increase speed but are there commands in the code which will prevent the screen from updating. I know activating or selecting things causes the screen to change. Also, any inputs to make this faster/better would be greatly appreciated.

Sub compare_lists(droparray() As String, addarray() As String, userrange1 As Range, userrange2 As Range)

Dim Rng As Range
Dim x As Long
Dim cellitem As Range
Dim junk1 As String
Dim y As Integer
Dim junk2 As Boolean

x = 1
'Compares Current list of students to "new" downloaded list of students
For Each cellitem In userrange1
            'This is the downloaded new list of students
         With userrange2
             Set Rng = .Find(What:=cellitem.Value, _
                                LookIn:=xlValues, _
                                LookAt:=xlWhole, _
                                SearchOrder:=xlByRows, _
                                SearchDirection:=xlNext, _
                                MatchCase:=False)
            If Not Rng Is Nothing Then
                Application.Goto Rng, True
                'junk1 = Rng.Offset(0, 0).Value
            Else
                droparray(x) = cellitem
                x = x + 1
            End If
        End With
Next cellitem


x = 1
'Compares New List of students to current list of students
For Each cellitem In userrange2
        'This is the downloaded new list of students
         With userrange1
             Set Rng = .Find(What:=cellitem.Value, _
                                LookIn:=xlValues, _
                                LookAt:=xlWhole, _
                                SearchOrder:=xlByRows, _
                                SearchDirection:=xlNext, _
                                MatchCase:=False)
            If Not Rng Is Nothing Then
                Application.Goto Rng, True
                'junk1 = Rng.Offset(0, 0).Value
            Else
                addarray(x) = cellitem
                x = x + 1
            End If
        End With
Next cellitem

End Sub

[Richard Buttrey]

Hi,

There is a small time overhead involved every time you jump backwards and forwards to the Excel Applications from the VBE and vice versa. So depending on the number of cells you're processing, it might be better to read all the cell values into a VBA array, process them entirely within the VBE and then finally write the results back to the Excel App.

HTH

[welchs101]

Hi and thanks.

Just to clarify, did you mean to read BOTH lists into individual arrays.........and process them using the arrays.

David

[welchs101]

Anyone have a good / fast way to compare to arrays ?

[shg]

Maybe as attached. The lists of old and new students should be culled for duplicates and trimmed before running.

If you try several methods, I'd be curious as know the timing results.

You could also just use two columns of formulas ...

[welchs101]

thanks SHG............ i will check it out.

DAvid

[Kenneth Hobson]

This is what I used to speed things up.
http://vbaexpress.com/kb/getarticle.php?kb_id=1035