You want to record 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 way the next time.

In just a moment you are about to learn how to record a dynamic Excel Macro using relative references and some mouse techniques.

If you still don’t know how to record a macro in Excel don’t be afraid because recording Excel Macros is simple!

Here’s how to do it…

1. How to record a macro in Excel that works dynamically on different array sizes and layouts
2. How to use a dynamic macro you recorded in Excel

How to record a macro in Excel that works dynamically on different array sizes and layouts

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 run it repeatedly easily)
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. On Excel 2016 go to Developer>Code>Use Relative References). Do this before you perform any movement of the Excel cursor around your spreadsheet
6. Record one instance of the dynamic Excel 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.

Now it’s time to use the dynamic macro you just recorded.

How to use a dynamic macro you recorded in Excel

Use your dynamic 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 Excel Macro will be repeated effortlessly.

For instance, you can use this dynamic macro recording technique to convert raw data like this one:

Into table layout like this one:

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 learn how to write Excel Macros from scratch.