Optimize my VBA code

I have following code working fine but I am hoping it can be modified to make it run faster. Currently it takes almost five minutes to run.
I have two worksheets. Data tab gets new update of currently open projects. Active tab needs to be updated.

The basic idea is to update worksheet Active in two ways by looking at records in Data tab.
1. If the record does not exist in Active tab [new project], that row [from Data tab] gets added at the bottom of Active tab.
2. If the record exists than update columns B:X and Z:AG. (data in column Y needs to remain, it's a manual entry).

I have learned a lot here and I would really appreciate any tips I can get to optimize this code.

Please Login or Register  to view this content.

Hi modytrane,

Instead of going through each row one by one why not try this....

Take the entire table and append it on the bottom of the other table. Then do an Advanced Filter using "Unique Records Only" on the combined table. This should leave only one of the duplicates and what I think you want.

Does this make sense? Do it a few times manually and then write the VBA to accomplish the Advanced Filter if you need to use VBA. Native Excel functions are faster than VBA.

I have been playing around with this and just figured out something. Maybe it will help others as well.

I turned of calculation [set it to manual] just before entering this code and turned it back on after exiting.
Made the code much faster. Seems like it was re-calculating after every step of the loop.

I still welcome any other tips I can get.

Here is a better site explaining what you need

http://cpearson.com/excel/optimize.htm

Marvin
Thanks for the tip.
Here's the question I have.
Once I combine the tables, old and new records will be in one table. Then when I filter for unique only, How do I ensure that only the newer record stays and older one gets deleted? There are no date or time stamp in the record to indicate which is newer. All I know is that Data tab has latest download.

Thanks again.

Marvin,
Thanks for the link. Nice tips there. I used one for calculation as stated above. I'll keep this for reference in the future.

When the Advanced Filter does its thing, using Unique Only, the top/first one is kept. If you put the newest table on the bottom (with dups in it) only the above records will be kept.

After reading your question again, you may want to put the newer table on top and then do the advanced filter on the combined table.

Hope this helps.

It does help to know that Advanced filter keeps top or first unique value.

Thanks