I'm redesigning a workbook that was designed poorly by the original creator and I'm trying to figure out the best way to save data when the data entry is done. I could probably figure out how to test each solution, but I'm hoping that someone has already done that and save me the time (I'm trying to save the client as much as possible).

I have a worksheet that will keep running totals of over 1600 items (4 columns for each item) and every 3 months there always seems to be more items added. I have an additional 4 columns on the same sheet that will show the new values to be saved when the user clicks the "save" button. So all my macro has to do is just copy the data from the 4 edit columns into the 4 permanent columns.

There are 2 issues that I have to deal with.

1) Only about 1/7 of the 1600+ items will be saved when the user clicks the "save" button.
2) About 3/4 of the items have been discontinued over time, so the running totals will be 0. (I will eventually change the data entry form so that it doesn't include the discontinued items, but until then they will still be in the way).

1) Would it be better to just copy the entire columns from the edit columns into the permanent columns (6/7 of the cells would be identical to what's already there), or would it be better to gather into a range (using Union()) only the cells that have changed and just copy the smaller range? IOW would it take more time to cycle through all the rows looking for the items that will be saved than it would take to just copy the entire columns? I will have EnableEvents turned off. There will only be one formula in the workbook that refers to each cell in 1/7 of the permanent data and only one formula referring to those - so roughly 1000 direct formulas and 1000 2nd level formulas would need to be recalculated.
2) If I don't save all the 0s for discontinued items, the formulas referring to the permanent data would have to perform a lookup twice in order to avoid N/As, and I would have to have the macro check to see if it needs to add a new row for data that has never been saved before, but it might be quicker when the user "saves" the data.
If I save all the 0s, then the formulas would only have to do 1 lookup, but it might take longer to "save" the data and refresh the downstream formulas.

I think I have described the situation well enough, but I could probably make a small workbook if I need to.