Making the Most of the Excel Macro Recorder Feature

Did you ever think to yourself, “There should be a shortcut for that!” Well, even if there isn’t, there can be! All you may have to do is record a macro.

A word of caution before we begin: The one thing you can be sure of, as new versions of Excel are released, is that VBA (Visual Basic for Applications) will change.  What does this have to do with Excel macros? Regardless of how you create your macros, behind the scenes, VBA is created.  So, while it’s true, if you’re the programmer type, you can write VBA to make your workbooks do just about anything, it is recommended that you use the Excel macro recorder for short, sweet, timesaving, error-reducing routines that give you back hours in your day and sanity in your processes.

Scenario: Too Tall/Too Small

Sometimes when you open a worksheet, you see pound signs (###) where there should be numeric data. Most of the time this is because the column is not wide enough to display the contents. A related problem is one where the rows are too short or too tall for the contents. Both of these issues can be solved with a crafty little macro that automatically right sizes columns and rows in a worksheet.

The Steps

In general, before recording a macro, you will want to write down your steps. You can also type them into a Notepad or Wordpad file. Then, rehearse them so you are confident with the keystrokes. Make any modifications needed to your steps to accommodate how this macro might be used in the future. Then, you are ready to record it!

1. Open up any worksheet with numerical data in it. Reduce the width of any one column with numeric data until you see the pound signs. Also, select a few worksheet rows and either make them too tall or too narrow.
2. Practice the keystrokes you will record. They are:
   1. Cornerstone (selects all cells in the worksheet)
   2. Double-click between two column letters (look for two headed black arrow)
   3. Double-click between two row numbers (look for a two headed black arrow)
   4. Press Ctrl+Home to return to cell A1

3. If this corrected your improperly sized columns and rows, you are ready to begin recording. Press Ctrl+Z or use the Undo button to return to its “problem” state, and try again.
4. Once you’re confident with the steps, you can begin recording. On the View tab, in the Macros group on the far right of the View ribbon, click the Macros button and choose Record Macro….
5. Name your macro. Use only alphanumeric characters and the underscore (_) symbol. We’ll call ours RightFitSheet.
6. Assign a shortcut by pressing the keystrokes you would hit after pressing Ctrl. You’ll notice that’s already there. In our example, we’ll press Shift and F, to create the shortcut Ctrl+Shift+F. You can also add a button to your Quick Access Toolbar to run the macro after the fact.
7. Record this in your Personal Macro Workbook to be able to run it on any worksheet you want.
8. Type a description, or copy paste your “script” from Notepad or Wordpad right in here if its brief enough.
9. Click OK.

10. Repeat steps a-d in step 2 above.
11. Click the stop button at the bottom in the Status Bar, or on the View tab in the Macros group by clicking the Macros button and Stop Recording.

Now go ahead and change the sizing on your worksheet in a different way and run your macro. No matter if your columns or rows are too tall or too small, your new macro should fix it!