 |
 |
| About MrExcel |
| Consulting Services |
| Learn Excel Resources |
| Challenge of the Month |
| MrExcel Seminars |
| MrExcel Store |
| Podcast |
| Search |
| Media |
| Contact |
| Home |
 |
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.
- 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
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.