How to Remove All Blank Columns in Microsoft Excel

There are several ways to remove empty columns in a Microsoft Excel file. Why would you need to do this? – Simple.

Occasionally, data you import from web pages may cause a large number of columns to appear even though they are not being used. You see this happen with CSV files and .txt files just as often.

When this happens, deleting columns manually may not always be easy. Of course, if you only have two or three empty columns, you can remove them manually. But what if your imported project creates 57 empty, non-continuous columns? – For this you will need an automated process.

Using the VBA macro

The first method is to use a VBA macro.

1. Access your Excel file.
   
2. Hold Alt and F11 together.
   
3. Wait for the Microsoft Visual Basic for Applications window to appear.
   
4. Click on Insert.
   
5. Select Module.
   
6. Paste the following lines of code into the window.
   Sub DeleteEmptyColumns()
   'Updateby20140317
   Dim rng As Range
   Dim InputRng As Range
   xTitleId = "KutoolsforExcel"
   Set InputRng = Application.Selection
   Set InputRng = Application.InputBox("Range :", xTitleId, InputRng.Address,Type:=8)
   Application.ScreenUpdating = False
   For i = InputRng.Columns.Count To 1 Step -1
   Set rng = InputRng.Cells(1, i).EntireColumn
   If Application.WorksheetFunction.CountA(rng) = 0 Then
   rng.Delete
   End If
   Next
   Application.ScreenUpdating = True
   End Sub
   
7. Press F5 to compile and run the macro.
   
8. Enter the appropriate working range in the dialog window.
   

   The working range or data range is the specific interval between the columns that you want to target. The format is $A$1:$J$12. The letters correspond to the column and the numbers correspond to the rows.

   
   
   

   If you drag this with your mouse or by holding down the Shift key and using the arrow keys, you will notice that:

   $A$1 – Top corner
   $J$12 – Lower corner

   You can't select the data range before you start downloading the macro because it won't stay selected.

9. Press OK.
   

After that, all empty columns should be cleared and all filled columns should be side by side.

Use Excel tools

Obviously, Excel wouldn't be such a powerhouse if it didn't have great sorting capabilities. You can use the Delete drop-down menu to delete entire rows, columns, or blank cells.

1. First select the data range
2. Press F5
3. Click on Special
   
4. Select the Blanks option
   
5. Click OK (This selection will ensure that all blank cells are selected in the targeted range)
6. Go to the Home tab
7. Select the Delete drop-down menu under the Cells tool group
8. Select Delete Cells
9. Select Shift Cells Left to delete and rearrange columns
10. OK
    

Now the empty cells of the empty columns should have disappeared and all the other rows would be brought together.

You can use the same approach to delete entire rows. However, instead of moving the cells to the left, you select the other option.

Select Shift Cells Up to delete and rearrange rows

Depending on the version of Excel you are using, you may get different wordings. But in any case, the first two options in the Delete Cells menu are always the same.

This method no longer removes all blank cells from the selection. Prior to Excel 2013, this inadvertently deleted even blank rows, which would usually mess up sorting.

Now the problem no longer occurs. Therefore, if you also want to remove the rows, you can do so by selecting the data range again and following the previous steps. Then just select to move or delete cells up instead of left.

Other easy-to-do sorting tasks

Although technically using the Excel toolbar to delete blank columns and rows seems easier, the VBA macro method is foolproof, which means you can use it even in older versions of Microsoft Excel. .

Using the same VBA module or Go To function menu, you can do so much more in Excel. Do you have certain formulas that are no longer relevant? – You can also delete or rearrange them accordingly.

You can also delete unnecessary comments or all comments in your project if you don't want them to appear during your presentation. Look into VBA if you want to be an advanced Excel user.

One last thought

Over the years, a wide range of add-ons have appeared online. Some of them allow you to take even more shortcuts when sorting large spreadsheets. However, these apps are rarely free and not worth it for simple tasks like deleting blank rows, cells, and columns.

Also, if it was that difficult, Microsoft would have simplified the process by now or created even more comprehensive guides on Excel sorting.