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:

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

Sub DeleteROPBlanks()
	'
	' DeleteROPBlanks Macro
	Dim Col As Long, ColCnt As Long, Rng As Range

	Application.ScreenUpdating = False
	Application.Calculation = xlCalculationManual

	On Error GoTo Exits:

	If Selection.Columns.Count > 1 Then
		Set Rng = Selection
	Else
		Set Rng = Range(Columns(1), Columns(ActiveSheet.Cells.SpecialCells(xlCellTypeLastCell).Column()))
	End If
	ColCnt = 0
	For Col = Rng.Columns.Count To 2 Step -1
		If Application.WorksheetFunction.CountA(Rng.Columns(Col).EntireColumn) < 2 Then
			Rng.Columns(Col).EntireColumn.delete
			ColCnt = ColCnt + 1
		End If
	Next Col

	Exits:
	Application.ScreenUpdating = True
	Application.Calculation = xlCalculationAutomatic

End Sub

Mac:

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"
Open spreadsheet in Excel
Excel > Preferences > Ribbon > check Developer > OK
On the ribbon menu, click Developer > Editor
Insert > Module
Paste VBA code shown above and then close Module window
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.