Murphy Mac - Screencasts and Tutorials » Page 'Record Smarter Excel Macros'

Record Smarter Excel Macros

Share

Excel makes macro creation pretty easy with a capable recorder mechanism. You turn on the recorder, perform some activity in Excel, and the macro is written for you. This screencast was created on a Mac, but it wouldn’t be much different on a PC.

Excel MacrosThe main point of the screencast: How to use offsets to move the active cell around the worksheet. If you click on a bunch of cells while recording your macro the code will contain those exact cells. When you run the macro the same cells will be selected every time. Maybe that’s not what you want.

Example – what if the worksheet gets larger all the time, and you always want the macro performed on the last row? Or if you’re frequently manipulating cells to the right of the active cell? As long as your worksheet is laid out consistently macros can help.

In the screencast, Murphy starts by selecting an anchor cell at the top of a list and then using a keyboard shortcut to move to the bottom. The macro records the movement this way and always performs its actions at the bottom of the list. Murphy also shows how to move the cell pointer a certain number of cells left, right, up, or down from the current cell.

The bottom line: Using relative references in your macros allows the code to be effective as your spreadsheet grows. Combining relative references with an absolute starting point opens up a lot of possibilities.

PS: Voter turnout at the polls has been weak!!

Watch Now | Permalink

Leave a comment