How to remove all blank columns in Microsoft Excel

Device links

• Android
• Mac
• Device missing?

From time to time, data you import from web pages may result in a large number of columns appearing even if they are not used. This happens with CSV and .txt files with the same frequency.

When this happens, manually deleting columns may not always be easy. Sure, if you only have two or three empty columns, it’s okay to delete them manually. But what if your imported project creates 57 empty, non-continuous columns? – For that, you will need an automated process.

Using VBA macros

The first method involves using a VBA macro.

1. Go to your Excel file.
   
2. Hold Alt + F11 together.
   
3. Wait for the Microsoft Visual Basic for Applications window to appear.
   
4. Then click 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 range is the specific interval between 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.
9. Press OK.
   

After that, all empty columns should be deleted and all filled columns should be next to each other.

Using Excel tools to remove blank columns

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

1. First, select the data range and press F5.
2. Then click Special.
   
3. Now, select the Blanks option.
   
4. Click OK (this selection will ensure that all blank cells are selected in the target range).
5. Go to the Home eyelash.
6. Select the Delete drop-down menu in the Cells tool group.
7. Select Delete cells.
8. Select Move cells to the left to remove and rearrange columns.
9. Click OK.
   

Now the empty cells in the blank columns should be gone and all other rows are closer together.

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

Select Shift cells up to remove and rearrange rows.

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

This method no longer removes all blank cells from the selection. Before Excel 2013, this inadvertently deleted even empty rows, which usually ruined sorting.

Now the problem no longer occurs. Therefore, if you want to get rid of the rows as well, you can do so by selecting the data range again and following the steps above. Then simply select move or delete cells up instead of left.

Remove blank columns in Android

Although the process is similar to removing blank columns in Excel on Windows, Mac, and Android, here’s a quick overview of how to do it on Android.

1. Open the Excel spreadsheet you want to edit and tap the header of the column you want to edit.
2. Now, select Delete from the menu that appears.

Other easy-to-perform classification tasks

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

Using the same VBA module or the Go To function menu, you can do 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 from 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.

A final thought

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

Plus, if it were that difficult, Microsoft would have already simplified the process even further or created even more extensive guides on sorting in Excel.