MrExcel.com - Your One Stop for Excel Tips & Solutions

Record Modify Run Excel Macro

October 01, 2002 - by

This is the 19th weekly Excel tip at MrExcel.com. Many of the Excel tips involve some sort of macro trick. This week, for the Excel users who have never written a macro, I offer a primer on how to record and then customize a useful Excel macro.

Sample Address Data
Sample Address Data

Let's say you have 400 rows of address data like that shown in the top figure at the left. The name field is in column A, the street address in column B, and the city in column C.

Your goal is to convert the data to a single column like that shown in the second figure.

This simple problem will be used to illustrate how to record, modify, and then run a simple macro.

For Excel 95 users: After recording the macro, Excel will put your macro on a sheet called Module1 in your workbook. You can just click on the sheet to access the macro.

Although there are 400 records in this worksheet, I want to record a tiny bit of the macro that takes care of just the first address. The macro will assume that the cellpointer is on the first name. It will insert three blank rows. It will copy the cell to the right of the original cell to the cell underneath the original cell. It will copy the city cell to the cell 2 rows under the original cell. It should then move the cell pointer down so it is on the next name.

The key is to think this process out before you record it. You don't want to make a lot of mistakes when recording the macro.

So, put your cell pointer in cell A1. Go to the menu and select Tools > Macro > Record new Macro. The Record Macro dialog suggests a name of Macro1. This is fine, so hit OK.

The Excel macro recorder has one very stupid default setting which you absolutely must change in order to have this macro work. In Excel 95, go to Tools > Macro > Use Relative References In Excel 97-2003, click the second icon on the Stop Recording Toolbar. The icon looks like a tiny worksheet. A red cell in C3 points to another red cell in A3. The icon is called Relative Reference. When this icon is 'on', there is some color surrounding the icon. The icon remembers the last setting from the current Excel session, so you might have to click it a couple of times to figure out which method is on or not. In Excel 2007, use View - Macros - Use Relative References.

OK, we are ready to go. Follow these steps:

  • Hit the down arrow once to move to cell B1.
  • Hold down the shift key and hit the down arrow twice to select rows 2, 3, and 4
  • From the menu, select Insert, then select Rows to insert three blank rows.
  • Hit the up arrow and then the right arrow to move to cell B2.
  • Hit Ctrl X to cut cell B2.
  • Hit the down arrow, the left arrow, then Ctrl V to paste into cell A2.
  • Hit up arrow, right arrow, right arrow, Ctrl X, left arrow, left arrow, down arrow, down arrow, Ctrl V to move C1 to A3.
  • Hit the down arrow twice so that the cell pointer is now on the next name in Row A5.
  • Click the "Stop Recording" icon on the toolbar to stop recording the macro.

Well, you have recorded your first macro. Let's take a look. Go to Tools > Macro > Macros. From the list, highlight Macro1 and press the Edit button. You should see something that looks like this. 

Sub Macro1()
'
' Macro1 Macro
' Macro recorded 4/18/99 by MrExcel Reader
'
'
    ActiveCell.Offset(1, 0).Range("A1:A3").Select
    Selection.EntireRow.Insert
    ActiveCell.Offset(-1, 1).Range("A1").Select
    Selection.Cut
    ActiveCell.Offset(1, -1).Range("A1").Select
    ActiveSheet.Paste
    ActiveCell.Offset(-1, 2).Range("A1").Select
    Selection.Cut
    ActiveCell.Offset(2, -2).Range("A1").Select
    ActiveSheet.Paste
    ActiveCell.Offset(2, 0).Range("A1").Select
End Sub

Hey, if you are not a programmer, that probably looks pretty intimidating. Don't let it be. If there is something you don't understand, there is excellent help. Click your cursor somewhere in the keyword Offset and hit F1. Provided you installed the VBA help file, you will see the help topic for the Offset keyword. The help tells you the syntax of the statement. It says that it is Offset(RowOffset, ColumnOffset). Still not very clear? Look for the green underlined word "example" near the top of the help. Excel's VBA examples will allow you to learn what is going on. In the example of Offset, it says to activate the cell two rows below and three rows to the right of the current cell, you would use: 

ActiveCell.Offset(3, 2).Activate

OK, so that is a clue. The offset function is a way of moving around the Excel spreadsheet. Given this bit of information, you can kind of see what the macro is doing. The first offset(1, 0) is where we moved the cellpointer down to A2. The next Offset is where we moved up one row (-1 rows) and over 1 column. You may not understand anything else in the macro, but it is still useful.

Go back to the Excel worksheet. Put your cell pointer in cell A5. Choose Tools > Macro > Macros > Macro1 > Run. The macro runs and your second address is formatted.

You may be saying selecting this whole long big string of commands is harder than just formatting by hand. OK, then do Tools > Macro > Macros > Options. In the shortcut box, say Ctrl + w is the shortcut key for this macro. Click OK, then dismiss the Macro dialog with Cancel. Now, when you hit Ctrl w, the macro will run. You can format an address in a single keystroke.

Are you ready for the big time? How many addresses do you have left? I hit Ctrl w a few times, so I have 395 left. Go back to your macro. We are going to put the entire macro code in a loop. Insert a new line that says "Do Until activecell.value = "" " before the first line of macro code. Insert a line that says "Loop" before the End Sub line. The Do loop will execute everything between the Do and the Loop line until it runs into a blank line. The macro now looks like this: 

Sub Macro1()
'
' Macro1 Macro
' Macro recorded 4/18/99 by MrExcel Reader
'
'
Do Until ActiveCell.Value = ""
    ActiveCell.Offset(1, 0).Range("A1:A3").Select
    Selection.EntireRow.Insert
    ActiveCell.Offset(-1, 1).Range("A1").Select
    Selection.Cut
    ActiveCell.Offset(1, -1).Range("A1").Select
    ActiveSheet.Paste
    ActiveCell.Offset(-1, 2).Range("A1").Select
    Selection.Cut
    ActiveCell.Offset(2, -2).Range("A1").Select
    ActiveSheet.Paste
    ActiveCell.Offset(2, 0).Range("A1").Select
Loop
End Sub

Go back to your Excel sheet. Put the cell pointer on the next name. Hit Ctrl w and the macro will format all of your records in a few seconds.

The authors of Excel books say that you can not do anything useful by recording a macro. Not true! For the person who was going to have to cut and paste 800 times, this macro is very useful. It took a few minutes to record and customize. Yes, professional programmers will point out that the code is horribly inefficient. Excel puts a whole bunch of stuff in there that it does not need to put in there. Yes, if you knew what you are doing, you can accomplish the same task with half the lines which will run in 1.2 seconds instead of 3 seconds. SO WHAT? 3 seconds is far faster than the 30 minutes the task would have taken.

Some more tips for beginning macro recorders:

  • The apostrophe is used do indicate a comment. Anything after the apostrophe is ignored by VBA
  • This is object oriented programming. The basic syntax is object.action. If a object oriented compiler were playing soccer, it would say "ball.kick" in order to kick the ball. So "Selection.Cut" says to do an "edit > cut" on the current selection.
  • In the above example, the Range modifiers are relative to the active cell. If the active cell is in B2 and you say "ActiveCell.Range("A1:C3").Select", then you select the 3 row by 3 column area starting in cell B2. In other words, you select B2:D4. Saying "ActiveCell.Range("A1")" says to select the 1 x 1 cell range starting with the active cell. This is incredibly redundant. It is equivalent to saying "ActiveCell.Select".
  • Save your workbook before running a macro for the first time. This way, if it has an error and does something unexpected, you can close without saving and revert back to the saved version.

Hopefully this simple example will give you novice macro recorders the courage to record a simple macro the next time you have a recurring task to perform in Excel.