Watch three tricks to help the Macro Recorder do a better job in Excel Video 405. The first trick is to switch between the absolute and relative references we discussed in Excel Video 404. Watch how I start with an absolute reference to cell A1 so that no matter where I am in the spreadsheet, my macro starts at cell A1. Once we’re there, I switch to relative references so that the second trick will work.
Trick number two is to use arrow keys in combination with keys like End, Shift and Ctrl to navigate around the spreadsheet. With the macro set to relative references, these keys allow my macro to deal with varying sizes of data. It’s worth reviewing Excel Videos 208-210 to learn more keyboard navigation tricks to help your macros work with relative reference so that whether you have 10 rows or 10,000 rows, your macro works as you expect.
The third trick is write the sum formula instead of using AutoSum. Watch how the sum formula starts with =SUM($B$2 because I know my amount column will always be column B and my formula will always start with B2. I can let the rest of the sum formula (B16 in the example) be a relative reference as the number of rows in my data changes.
This is a long video, but if you can get the Macro Recorder to do most of the work for you and do it correctly, macros become lots easier to write and manage. We’ll look at the VBA code the Macro Recorder creates in the next Excel Video.
