Automate a Tedious Task with Excel Macros

May 23, 2006

This episode was the one where Tracy Syrstad, Nate Oliver and I all went to the Toronto Blue Jays game with a great group of Toronto-area folks from the site.

The next day, Tracy, Nate, and Tracy's husband John and I toured the TechTV studios:

From left to right, that is John, Nate, Tracy, and Bill

I wanted to do a mail merge from Excel to Word. However, when the person sent me the data, it was in the wrong sequence. Instead of having the data go across the columns, the data had the information going down the rows of column A.

While you could manually fix all of these, it would be a lot of cutting and pasting. This is the perfect task for automating with a macro.

If you have never used macros before, go to Tools - Macro - Security and change the setting to Medium.

The goal is to record a tiny macro that will solve one record. You want the cell pointer to be on the name to start. The macro should move the two fields over, delete the three blank rows, and end up on the next name. This way, you can run the macro over and over.

Put the cell pointer in A1.
Choose Tools - Macro - Record new Macro
Give the macro a name (no spaces allowed). Choose a shortcut key. Since you will only be using the macro for this task, something like Ctrl + a is easy to remember.
You will see a tiny toolbar. This is the Stop Recording Toolbar. It only has two buttons. The first button is the Stop Recording button.
The second button is the Relative References button. If you simply recorded the macro now, Excel would hard-code that you wanted to always move

A2 to B1 and A3 to C1. This works fine for one record, but you need the macro to work for all records. Turn on Relative Recording by clicking the Relative Recording button. It is a toggle, so you want it to look like it is pressed in.
Everything you do will be recorded. Try to use keystrokes instead of the mouse.
Hit the down arrow to move to B2.
Ctrl + x to cut
Hit up arrow, right arrow to move to A1.
Ctrl + V to paste
Left, Left, Down, Down, Ctrl + X to cut the city row
Up, Up, Right, Right, Ctrl + V to paste the city in column C
Left, Left, Down to move to row 2.
Hold down the Shift key while you hit down, down to select rows 2, 3, 4
Alt + edr and enter to choose Edit - Delete - Entire Row - OK
At this point, you've almost finished the macro. The problem is that you have three cells selected and you just want the name selected.
Hit the up arrow and the down arrow to select just the name of the second record.
Click on the Stop Recording button

It is time to try out the macro. Save the workbook in case something goes wrong here.

Type Ctrl + a to run the macro once. If all went well, the macro will have fixed the next name.

If that worked, you can now hold down Ctrl + a until all of the records are fixed.

This tiny macro solved a real-life problem. The key to success was using the Relative Recording mode!

If you take the time to learn VBA, you could solve these problems, plus far more complicated problems. Check out Tracy and my book - VBA and Macros Microsoft Excel 2016.