Easy and Useful Macro: Right Fit Worksheet — Business Management Daily: Free Reports on Human Resources, Employment Law, Office Management, Office Communication, Office Technology and Small Business Tax Business Management Daily
  • LinkedIn
  • YouTube
  • Twitter
  • Facebook
  • Google+

Easy and Useful Macro: Right Fit Worksheet

Get PDF file

by on
in The Office Tech Pro

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.

Columns too narrow, rows too tall and 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.
    Record macro dialog box

    Record macro dialog box

  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.
    Right size columns and rows

    Right size columns and rows

  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.

{ 1 comment… read it below or add one }

Bert May 2, 2012 at 9:05 am

Nice macro. I have been trying to create a macro that does a number of thing:
1. Automatically load a specific file when I load Excel
2. Copies info from one worksheet to another, then moves my cursor to the next line.
So far no success. Can you help me?


Leave a Comment

Previous post:

Next post: