Optimization should be the last phase of any development. My two favorite rules used to be:
1. Make it work first before make it work fast.
2. a) Make it work
b) Make it work correctly
Excel lacks a nice and decent profiler. I suggest to buy and to use Charles Williams' FastExcel ©. His website Decision Models is a good source of hints how to optimize your spreadsheet or your VBA code.
For standard approaches to speed up your code have a look at SystemState, please. It provides an easy-to-use way to set calculation to manual and to switch off screen updating, together with the feature to restore previous conditions later.
Finally have a look at the golden VBA rules by OZGRID.