Subscribe / Unsubscribe Enewsletters | Login | Register

Pencil Banner

5 must-know Excel macros for common tasks

J. D. Sartain | Sept. 11, 2014
If you're not using macros, you're ignoring one of Excel's most powerful features. Macros save you time and spare you headaches by automating common, repetitive tasks. And you don't have to be a programmer or know Visual Basic Applications (VBA) to write one. With Excel 2013, it's as simple as recording your keystrokes.

6. Select Delete>Delete Sheet Columns. Press CTRL+Home. Stop Recording, save the worksheet as 3Names2; open 3Names again and run the macro.

Repetitive Text

If you're typing the same information 10 times a day, you're begging for a macro. Even if that information is brief, a macro does it in seconds and ensures accuracy. This macro adds your company info to the top of a worksheet and inserts the current date. Open a new worksheet, name and define your macro, then record these keystrokes.

1. Press CTRL+Home. Hold down Shift, then Right Arrow twice. Select Home. From the Alignment group, select Merge Across. Enter this formula in cell A1: =TODAY()Enter, Up Arrow. Select Home>Format>Format Cells>Date. Choose a date format from the list, click OK. Press Down Arrow twice.

2. Type the repetitive information and press Enter at the end of each line. Press Down Arrow. Select Developer>Stop Recording. Delete it all, unmerge cells A1 through A3, then run the macro. Save the worksheet.

Remove Blank Rows

A worksheet filled with blank rows is impossible to manage, sort, or calculate. The first step is to instruct the macro to highlight the spreadsheet data only, then select and remove the blank rows. Once that's accomplished, you can easily manage the data.

Open a file with blank rows, name and define your macro, then record these keystrokes.

1. Press CTRL+Home. Note: CTRL+A will not select all the data when blank rows are in the spreadsheet, but this macro will.

2. Select Home>Insert>Sheet Column. Press End, Down Arrow, Right Arrow, End, Up Arrow. Press CTRL+Shift-Home, Shift-Right-Arrow, CTRL+Shift-Right-Arrow. And the data range is properly selected.

3. Select Home>Find & Select>GoTo Special (or press CTRL+G, ALT+S). Click Blanks>OK and all the blanks highlight in gray. Select Delete>Delete Cells>Shift Cells Up>OK and the blanks vanish. Press CTRL+home, then select Delete>Delete Sheet Column to remove the extra column we inserted to highlight the spreadsheet without hardcoding cell addresses.

4. Stop Recording. Undo all steps, then run the macro. Save the worksheet.

 

Previous Page  1  2  3 

Sign up for CIO Asia eNewsletters.