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.

Excel Macro Relative Reference - Image 1

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.

Excel Macro Relative Reference - Image 2

VBA Code in Edit Mode:

Excel Macro Relative Reference - VBA Code 1

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.

Excel Macro Relative Reference - Image 3

VBA Code in Edit Mode:

Excel Macro Relative Reference - VBA Code 2

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

Local Seminars Related to this Topic:

Related Excel® Articles

Categories

Monthly Archives

Leave a Reply

Your email address will not be published. Required fields are marked *