TechRepublic member Melissa Guzzetta needed to create an easy way for two of her clients to clean up data they’d imported into Excel spreadsheets.
“Every month, these customers needed to download thousands of transactions from a proprietary program into Excel so they could use Excel’s database tools to manipulate and analyze the data,” she said. “For some reason, the results in both cases included hundreds of blank rows that needed to be deleted.”
Her solution was to create a simple macro that deleted the empty rows in one quick step. This was an easy task for Guzzetta, who is a MOUS Master Instructor and owns her own computer software training company, The Computer Tutor, in Costa Mesa, CA.
After reading instructions for creating a macro that instantly creates consistent headers and footers, she sent in her clever trick. Read on to find out how it works.
Check your version
These instructions were created using Microsoft Excel 2000. If you’re using a different version, the directions may vary.
Instructions for the “DeleteBlanks” macro
To create a macro that deletes blank rows from data, perform the following steps:
- Open a new Excel workbook and add some rows of data. Any type of data will do, but you’ll want to leave a few blank rows within the spreadsheet. Figure A illustrates the sample data I’ll use for this exercise.
- From the Tools menu, choose Macro | Record New Macro (see Figure B).
- The Record Macro dialog box will open. In the Macro Name text box, type a name for your macro (see Figure C). Remember, macro names cannot include spaces or special characters. For this article, I’ll call the macro DeleteBlanks.
You may also choose to create a shortcut key for this macro. You can use Ctrl+ any letter or Ctrl+Shift+ any letter. Be careful, though. This shortcut key will override any default Microsoft Excel shortcut keys. You’ll want to choose a letter that isn’t associated with another shortcut you use or may use in the future. For this exercise, I’ll have the macro activated by the shortcut Ctrl+k, so I’ll enter k in the Shortcut key box, as illustrated in Figure C. (A listing of keyboard shortcuts can be found under Keyboard Shortcuts in Excel’s Help section.)
- Choose Personal Macro Workbook from the Store Macro In drop-down list to ensure the macro will appear in all new workbooks, and click OK. A small Macro toolbar will appear on your spreadsheet. From this point, Excel will record everything you do and save it as part of the macro.
- Choose Edit | Go To and click the Special button. Choose Blanks, and click OK. The blank rows within the spreadsheet will be selected (see Figure D).
- Next, choose Edit | Delete and choose Shift Cells Up (see Figure E). Click OK. The blank rows will be deleted.
- The next step is to stop the macro recording process. Click Tools | Macro | Stop Recording, as shown in Figure F.
Performing the macro
Repeat this macro function in any spreadsheet by choosing Tools | Macro | Macros, clicking on DeleteBlanks, and clicking Run. If you created the shortcut, you may execute the macro by pressing [Ctrl]k. If you prefer, you may also choose to create a macro button for your toolbar.
What’s your favorite macro?