How to remove all blank columns in Microsoft Excel?

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

When this happens, removing columns manually may not always be easy. Of course, if you only have two or three empty columns, it’s fine to delete them manually. But what if your imported project creates 57 empty and non-continuous columns? – You need an automated process for that.

Using VBA Macro

The first method involves using a VBA macro.

  1. Go to your Excel file.
    111.1
  2. Delay Alt + F11 together.
    111.2
  3. Wait for the Microsoft Visual Basic for Applications window to appear.
    111.3
  4. Then click Insert.
    111.4
  5. Select module.
    111.5a
  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

    111.6
  7. Press F5 to compile and run the macro.
    111.7
  8. Enter the correct working range in the dialog box.
    111.8
    The range is the specific interval between columns 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 Okay.
    111.pf1

After that, all empty columns must be cleared and all filled columns must be next to each other.

111.pf2

Using Excel Tools to Remove Empty Columns

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

  1. First select the data range and press F5.
  2. Then click Special.
    111.pf3
  3. Now select the Blanks option.
    111.pf4
  4. Click OK (this selection will select all blank cells in the target range).
  5. Go to the House tab.
  6. Select the remove drop-down menu under the Cell Tools group.
  7. Select Delete cells.
  8. Select Shift cells to the left for deleting and rearranging columns.
  9. Click Okay.
    111.pf5

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

111.pf6

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

Select Slide cells up for deleting and rearranging rows.

111.pf7

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

This method no longer removes all empty cells in the selection. Before Excel 2013, this would unintentionally delete even empty rows, which would generally mess up the sorting.

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

Remove Empty Columns on Android

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

  1. Open the Excel spreadsheet you want to edit and tap the column header you want to edit.
  2. Select now remove from the menu that appears.

Other easy-to-do sorting tasks

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

With 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 them 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 in VBA if you want to be an Excel master user.

One last thought

Over the years, a wide variety of add-ons have appeared online. Some allow you to use even more keyboard 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.

Besides, if it were that hard, Microsoft would have simplified the process by now or created even more comprehensive guides on Excel sorting.

Leave a Reply

Your email address will not be published. Required fields are marked *