Earlier we shared an Excel Macro to delete blank rows from Excel WorkSheet. We thought an excel macro to delete blank columns should be as useful as the previous one was. Of course it is a tedious job to scroll over entire worksheet just to delete blank columns. So, here is a small excel macro to remove all the blank columns from selected data in excel sheet just in few seconds. This macro also updates the status bar.
First step is to create a Macro. For creating a macro, click on Tools in menu bar, select Macro and then Macros in sub menu as shown in Figure 1 below.
Clicking on Macros in Figure 1 will open the below window. Type the name of Macro as “DeleteBlankColumns” or a name of your choice and click on Create button.
Clicking on Create button will launch the Visual Basic editor as below.
Copy below code and paste it in the editor.
Dim i As Long
Dim noCol As Long
Application.ScreenUpdating = False
Application.StatusBar = "Deleting the blank columns..."
Application.DisplayAlerts = True
noCol = Selection.Columns.Count
For i = noCol To 1 Step -1
Application.StatusBar = "Deleting blank columns " & Format((1 - (i / noCol)), "0 %") & "..."
If WorksheetFunction.CountA(Selection.Columns(i)) = 0 Then
Application.StatusBar = False
Application.ScreenUpdating = True
Save this Macro by closing the editor.
Select the range of data and run the Macro by clicking on Tools->Macro->Macros. Select the Macro “DeleteBlankColumns” and click on “Run” button. All blank columns will be removed.
[Enlarge image for a better view by clicking on it]
Note: This macro will remove blank columns only if the entire column does not contain any data.
To customize this Macro further, follow below steps.
- Go to Tools->Macro->Macros. Select the Macro “DeleteBlankColumns” and click on “Edit” button.
- This will launch the Visual Basic Editor.
- Make the required changes.
- Save the Macro.
Note: Steps to Create, Edit and Run a Macro in Excel 2007 are shared here.
Hope you find this Macro useful. Your suggestions, feedback and queries are always welcome.