Alternative for my extremely slow code?

Hey guys,

I'm working on a workbook that is all about collecting data to run analyses on it.
This code comes from 2 source files, from which I copy all data and paste it in 2 different sheets in my data collection workbook(for each source 1 sheet).
The source file gets updated all the time, with rows being deleted, new rows added into it and existing rows being updated with more accurate information.
When I update my data collection it's common that I already have rows in my data collection which are present (and updated) in the source file.
For this reason I add a new colomn (A) with a formula that will always output the same value for a row (even when the row gets updated this value stays the same) before I run the code.

Now the code should look for this unique code in my data collection, and if present it should replace the row in my data collection with the updated row from the source file. If the value is not yet present in column A in my data collection sheet, it should be added below all existing rows.

Now I got this *working* code to do this:

Sub FromSourceToDataCollection()
Application.ScreenUpdating = False
Dim f As Range
Dim d As Range
  With Sheets("Data Collection")
    For Each cl In Sheets("Source Data A").Columns(1).SpecialCells(2)
      Set f = .Columns(1).Find(cl.Value, , xlValues, xlWhole)
      If f Is Nothing Then .Cells(Rows.Count, 1).End(xlUp).Offset(1).Resize(, 5) = cl.Resize(, 5).Value Else f.Resize(, 5) = cl.Resize(, 5).Value
    Next cl
  End With
With Sheets("Data Collection")
    For Each cl In Sheets("Source Data B").Columns(1).SpecialCells(2)
      Set f = .Columns(1).Find(cl.Value, , xlValues, xlWhole)
      If f Is Nothing Then .Cells(Rows.Count, 1).End(xlUp).Offset(1).Resize(, 5) = cl.Resize(, 5).Value Else f.Resize(, 5) = cl.Resize(, 5).Value
    Next cl
  End With
Application.ScreenUpdating = True
End Sub

Unfortunately this code operates very very slowly, it takes my computer 15 minutes to completely run it (and thats only for 30k rows, let alone if I will use this file for some time and there will be hundreds of thousants of rows)

Now I looked all over the internet for alternatives but I can't find it. The closest I came to it was using dictionary's, but I wasn't able to completely figure this out.

Does someone know a good alternative to complete this task in a shorter amounth of time (without letting my computer crash, something this happens with my current code much to often )?

Thanks already for taking the time to look into my question

Kind regards,
Warmerfare

Hi Warmerfare,
Probably I'd go rather into arrays than into dictionaries, but before start, I've got one suggestion and one request:
- consider posting the attachment with sample data (of course not 30k rows)
- follow forum rules, namely Rule 3 of our Forum RULES. Use code tags around code.
Posting code between [CODE] [/CODE] tags makes your code much easier to read and copy for testing, it also maintains VBA formatting.
Click on Edit to open your thread, then highlight your code and click the # icon at the top of your post window. More information about these and other tags can be found here