Slow Looping

  • Thread starter Thread starter Matt
  • Start date Start date
M

Matt

Could someone tell me how to compare one worksheet with another. I'm using
looping but this is just too slow. Could someone tell me how to compare one
sheet with another and drop matching reference numbers beside each matching
number as in the below code. I'm thinking maybe using the Find method would
be faster but am not sure how to use it even after looking at the help..
There is 12,000 rows on the first sheet and 35,000 on the second. Any help
would be much appreciated.


Sub MatchUp()

Dim wss As Object
Dim wsc As Object
Dim T, I
Set wss = Worksheets("Supplers")
Set wsc = Worksheets("11360")

I = 6
Do While wsc.Cells(I, 1) <> "END"
T = 6
Do While wss.Cells(T, 1) <> "END"
If -wsc.Cells(I, 7) = wss.Cells(T, 5) And wsc.Cells(I, 3) = wss.Cells(T, 3)
And wsc.Cells(I, 8) = "" Then
wsc.Cells(I, 8) = I
wss.Cells(T, 6) = I
End If

T = T + 1
Loop
I = I + 1
Loop
End Sub
 
Matt,

12,000 Finds will take time as well.

Couple of things you could do is

- turn off screen updating

Application.ScreenUpdating = False

- turn off automatic calculation

Application.Calculation = xlCalculationManual

Be sure to reset at some point though (True and xlCalculationAutomatic).

If this isn't enough, another way is to use a filter and create a test
column that can be filtered, and then delete rows that match. Post back if
you want to follow this option.

--

HTH

Bob Phillips
... looking out across Poole Harbour to the Purbecks
(remove nothere from the email address if mailing direct)
 
Back
Top