How to record a dynamic Excel macro

You want to create dynamic Excel Macros that work on variable-size arrays, right?

Maybe you recorded/created an Excel VBA Macro and worked awesome this time, but you are wondering if it will work the same next time.

In just a moment…

You will learn how to record a dynamic Macro using relative references.

Don’t feel scared, Macros are not that complicated!

Here’s how to do it…

  1. Put your cursor at the starting position of your raw data (Don’t include earlier mouse movements in your Macro)
  2. Launch the Record Macro dialog
  3. Assign a shortcut to your Macro (so you can repeat it easily – you will do a kind of manual loop)
  4. Click Ok on the Record Macro dialog
  5. Use relative references (On Excel 2003 do it this way: click the Relative Reference button on the Stop Recording toolbar; on Excel 2007 do it this way: go to View>Macros> Macros>Use Relative References). Do this before you perform any movement of your cursor
  6. Record one instance of the Macro (if you have one thousands blocks of data, record just one)
  7. Stop the Macro when your mouse is at the starting position of your next block of data (corresponding position to the location you started on step 1 of this tutorial)

Congratulations! You have recorded a dynamic Excel Macro.

Use your smart Macro this way:

Put your cursor at the starting position and then press the Macro shortcut. If you want to process N blocks of data, press CTRL + YOURKEY N times, or keep your shortcut pressed and the Macro will be repeated without effort.

For instance, you can use this technique to convert raw data…

Company name

Name

Address

Phone

Fax

Email

Company name

Name

Address

Phone

Into table layout…

Company name, Name, Address, Phone, Fax, Email

Your recorded Macros will work for one row or for one million rows, for one column or one hundred columns.

Want to go to the next level?

Then you need to create Excel Macros from scratch.