Easy and Useful Macro: Right Fit Worksheet

If you’ve never created a Macro before, this one will be an easy starting place. A macro can be a simple recording of keystrokes that can be played back over and over again. The purpose of this macro is to set the column widths and row heights to the right size to accommodate the data their holding. Start with a worksheet where you’ve narrowed a few columns enough to show # signs in the cells. You may also want to select a few rows and make them either too wide or too narrow.

1. Start on the View tab, in the Macro group and click the Macro button.
2. Select Record Macro Name it something easy, such as “RightFitSheet”.
3. Assign it a keyboard shortcut using your Shift key and a letter, like R for right fit. This way the shortcut will be Ctrl+Shift+R.
4. Change the “Store macro in…” field to Personal Macro Workbook. This way you can use it on any workbook.
5. When you click OK, you will be recording your keystrokes. It’s always a good idea to take your hands off of keyboard and mouse at this point. You will want to be very intentional about what you do next.
6. Select the cornerstone on the worksheet to select all cells (Ctrl+A twice works well, too). Be sure that all cells remain selected during the next two steps.
7. Double-click between two column headers. Be sure your cursor is positioned between the two letters and appears as a horizontal double arrow.
8. Now, double-click between two row headers. Be sure your cursor is positioned between the two numbers and appears as a vertical double arrow.
9. Press Ctrl+Home to place your cursor in cell A1. This step is optional, but it’s always good to know where your cursor is going to end up when your macro completes.

Now reduce the column and row sizes again and try the shortcut you created (Ctrl+Shift+R)! Want to understand more about macros? Attend our webinar on March 7, 2012 at 1PM Eastern.