MrExcel Publishing
Your One Stop for Excel Tips & Solutions

Excel 2019: Improve Your Macro Recording


October 30, 2019 - by Bill Jelen

Excel Improve Your Macro Recording. Photo Credit: Manuel Sardo at Unsplash.com

I loved the 1985 version of the Macro Recorder in Lotus 1-2-3. The code was hard to understand, but it worked. There are some defaults in the Excel Macro Recorder that cause misery for anyone trying to record macros. Here are three tips to make the macro experience possibly better.

Tip 1: Turn on Relative Reference for Every Macro.

Say that you start in A2 and record a simple macro that moves to A4. I would call that macro MoveDownTwoCells. But if you run this macro while the cell pointer is in J10, the macro will move to cell A4. This is rarely what you want to have happen. But you can change the behavior of the macro recorder by selecting View, Macros, Use Relative References before you record the macro.

There is a Macros drop-down on the View tab. The third choice is called Use Relative References.

If you record the MoveDownTwoCells macro with this setting turned on, the macro will work from any cell.


Caution

The setting stays turned on only until you close Excel. Tomorrow, you will have to turn it back on again.

Tip

What if you actually need to record a macro that always jumps to cell A4? Even with Relative References enabled, you could press F5 for Go To and use the Go To dialog to go to A4. The macro recorder will record code that will always jump to A4.

Tip 2: Use Ctrl+Shift+Arrow to move to the end of a data set.

Say that you want to add a total at the bottom of yesterday‘s invoice register. You want the total to appear in row 9 today, but tomorrow, it might need to be in row 17 or row 5, depending on how many rows of data you have.

Find a column that is reliably 100% filled. From the top of that column, record the action of pressing Ctrl+Shift+Down Arrow. Press the Down Arrow key one more time, and you will know you are in the row where the totals should be.

If this is a CSV file, you will always be in cell A1 when it is open. Press Ctrl+Shift+Down Arrow in the macro will get you to the last row with data today.

Tip 3: Type =SUM(E$2:E8) instead of pressing the AutoSum button

The macro recorder will not record the intent of AutoSum. When you press AutoSum, you will get a sum function that starts in the cell above and extends up to the first non-numeric cell. It does not matter if you have Relative References on or off; the macro recorder will hard-code that you want to sum the seven cells above the active cell.

Instead of using the AutoSum icon, type a SUM function with a single dollar sign before the first row number: =SUM(E$2:E8). Use that formula while recording the macro, and the macro will reliably sum from the cell above the active cell all the way up to row 2, as shown below.

With numbers in E2:E8, you would normally use a formula of =SUM(E2:E8). However, if you record the macro to use =SUM(E$2:E8), the formula will work for any size data set.

Title Photo: Manuel Sardo at Unsplash.com


Bill Jelen is the author / co-author of
Microsoft Excel 2019 Pivot Table Data Crunching

Use Excel 2019 pivot tables and pivot charts to produce powerful, dynamic reports in minutes instead of hours, to take control of your data and your business. Even if you’ve never created a pivot table before, this book will help you leverage all their remarkable flexibility and analytical power–including valuable improvements in Excel 2019 and Excel in Office 365.