speeding up Macro

[jwilson28]

Hi all,

I would really appreciate any help

I have been able to find and modify a macro to do what I need, however it takes so long that it would would be quicker to do what I want it to do manually.

I have up to 40 input values from A1:A40 which are from a barcode scanner in a worksheet called 'Module Movements'. I then need to compare these values to cells A1:A1000 in worksheet 'Sheet2' and delete any values that match the values in 'Module Movements'

It is currently taking 4min 22seconds to complete this task.



Sub DelDups_TwoLists()
Dim iListCount As Integer
Dim iCtr As Integer



iListCount = Sheets("sheet2").Range("A1:A1000").Rows.Count
For Each x In Sheets("Module Movements").Range("A1:A40")
For iCtr = 1 To iListCount
If x.Value = Sheets("Sheet2").Cells(iCtr, 1).Value Then
Sheets("Sheet2").Cells(iCtr, 1).ClearContents
iCtr = iCtr + 1
End If
Next iCtr
Next
Application.ScreenUpdating = True
MsgBox "Done!"
End Sub

Thanks

Jon

 

[Fluff]

That does what the OP originally asked for, then clears the dictionary for the OPs 2nd request.

 

[Rick Rothstein]

That does what the OP originally asked for, then clears the dictionary for the OPs 2nd request.

So the ultimate use is not to be in a stand-alone subroutine like you posted, correct?

 

[Fluff]

Not totally sure if the OP wanted two separate macros, or one to do both.
So I supplied one to do both, which can be changed if needed

 

[jwilson28]

Hi all,

Sorry for the late reply.
I asked for this in two separate macros as I will be using them several times through the workbook independent of each other. In this case I have just copied the second macro by Fluff onto the end of the first macro and it seems to work perfectly.

Thank you all for your help.