Excel Macro: Relative Reference

Excel records Macros in absolute mode by default. In absolute mode, a Macro will start working in the same cell it was in when it was recorded. If, when it was recorded, A1 was the active cell, then it will return to A1 and begin working from there no matter which cell was selected prior to starting the Macro.

When you turn on Relative Reference before recording a Macro, it will start the Macro “relative” to whatever cell is currently selected. This makes the Macro more flexible.

Example:

If you start recording the Macro in Cell A6 and insert a row:

With Absolute Reference – no matter what cell is selected the Macro will always insert a row at Row 6.

VBA Code in Edit Mode:

With Relative Reference – the macro will insert a row relative to the cell selected. If Cell A9 is selected, the macro will insert a row at Row 9.

VBA Code in Edit Mode:

Click the Use Relative Reference button on the Developer tab to toggle relative reference on and off.