Excel: Delete/remove blank/empty columns #

Or rather, delete columns that are blank or have data in the first/header row only. Tested in Excel 2003 (Windows) and 2011 (Mac).

Windows:

  1. Open spreadsheet in Excel
  2. Tools > Macro > Visual Basic Editor
  3. Paste the macro shown below (created by AnitaPita with a hand from jbeaucaire)
  4. File > Save
  5. File > Close and Return to Microsoft Excel
  6. Tools > Macro > Macros
  7. Click Run (if you have other macros in the spreadsheet, you'll need to select "DeleteROPBlanks" first)

Mac:

  1. If your copy of Office 2011 does not have VBA installed, you can add it by running the installer again, choosing Customize, and then checking only "Visual Basic for Applications"
  2. Open spreadsheet in Excel
  3. Excel > Preferences > Ribbon > check Developer > OK
  4. On the ribbon menu, click Developer > Editor
  5. Insert > Module
  6. Paste VBA code shown above and then close Module window
  7. Macros > Run (if you have other macros in the spreadsheet, you'll need to select "DeleteROPBlanks" first)

/windows | Jul 24, 2014


Subscribe or visit the archives.