Hello there,

I'm still quite new to VBA and I'm facing optimization problem with quite simple macro working with a lot of data.

To describe what I need to acheive:
I have two sheets of data. I need to take one sheet, check for data in another sheet and update it if needed. Visual usually works best so bellow you can find very simplified example.
As you might see, I have some data in sheet 1 and 2. I need to take ID from sheet 2, check for it in sheet 1 and see if it is there.
If yes, I need to update all cells on corresponding row in sheet 1 with data from 2. 2 does not have to contain all the data for the row, so I need to be sure I'm not removing anything from 1 by copying empty cells from 2 (as shown with ID 555).
If not, I need to add new ID and all it's data from 2 to 1 (as shown with ID 666, 777 and 888).
As shown with ID 111, sheet 2 does not have to contain all the IDs listed in sheet 1 and vice versa.

I have a brute-force macro simply comparing ID from 2 to IDs from 1 as strings deciding what to do in double loop, but this is terrible inefficient, since I have to work with for example 10 000 rows on sheet 1 and another 18 000 rows on sheet 2 (both have about 50 columns), which can end up as sheet 1 having 25 000 rows in total after the update (I don't care for sheet 2 when it is done).

So, any tips how to smoothen the code and reach best working speed would be much appreciated.

And here is the example I promised
sheet 1 (original data)
name surname ID status note
John Doe 111 pending
Jane Dee 222 ok married
Adam 333 ok
Default Name 444 pending
Peter Pan 555 check
sheet 2 (updated data)
name surname ID status note
Jane Dee 222 valid still married
Adam Brown 333 valid
Simon Whatisname 444 ok check
Peter Pan 555 ok
Arthur Novak 666 valid
Anna Marie 777 ok single
Lili White 888 ok check
sheet 1 (updated)
name surname ID status note
John Doe 111 pending
Jane Dee 222 valid
Adam Brown 333 valid still married
Simon Whatisname 444 ok
Peter Pan 555 ok check
Arthur Novak 666 valid check
Anna Marie 777 ok single
Lili White 888 ok check