:: infotechGuyz.com ::
> For the IT community by the IT community

Excel 2010 Macro Recorder Tricks

Using Excel 2010 Macro Recorder
Your macros may not always work after recording, but there are some tricks to increase your chances. However, you will get much closer by using the three tips demonstrated below.

 

Tip #1
Use Relative References Setting Usually Needs to Be On
Microsoft should have made this setting be the default. Unless you specifically need to move to Row 1 from the bottom of a dataset, you should usually leave the Use Relative References button in the Developer tab turned on.

 

Tip #2
Use Special Navigation Keys to Move to Bottom of a Dataset
If you are at the top of a dataset and need to move to the last cell with data, you can press Ctrl+down arrow or press the End key and then the down-arrow key.Similarly, to move to the last column in the current row of the dataset, press Ctrl+right arrow or press End and then press the right-arrow key.By using these navigation keys, you can jump to the end of the dataset, no matter how many rows or columns you have today.

 

Tip #3
Never Touch the AutoSum Icon While Recording a Macro
The macro recorder will not record the “essence” of the AutoSum button. It will instead hard-code the formula that resulted from pressing the AutoSum button. This formula does not work any time you have more or fewer records in the dataset. Instead, type a formula with a single dollar sign, such as =SUM(E$2:E10). By doing so, the macro recorder records the first E$2 as a fixed reference and starts the SUM range directly below the Row 1 headings. Given the active cell is E11, the macro recorder recognizes E10 as a relative reference pointing directly above the current cell.