3 tips to make the macro experience possibly better

3 tips to make the macro experience possibly better

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.

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.

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.

#evba #etipfree #kingexcel

📤You download App EVBA.info installed directly on the latest phone here : https://www.evba.info/p/app-evbainfo-setting-for-your-phone.html?m=1