Automate a Tedious Task with Excel Macros
Amber MacArthur, Bill Jelen, Leo LaPorte, Andy Walker on the set of Call for Help on TechTV Canada. The tip in this show is from VBA & Macros for Microsoft Excel.
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.
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 & Macros for Microsoft Excel. For the BEST TV show on technology, check out Call for Help. This tip was originally published on May 23, 2006 and aired on TechTV in Canada and Australia on May 22, 2006. The permanent URL for this page is http://www.mrexcel.com/tip127.shtml. If you are looking for show notes from another episode, visit my complete list of TechTV appearances.
MrExcel.com provides examples of Visual Basic procedures for illustration only, without warranty either expressed or implied, including but not limited to the implied warranties of merchantability and/or fitness for a particular purpose. The Visual Basic procedures on this web site are provided "as is" and we do not guarantee that they can be used in all situations.
Excel is a registered trademark
of the Microsoft® Corporation.
MrExcel is a registered trademark of Tickling Keys, Inc.
All contents Copyright 1998-2008 by MrExcel Consulting.