How To Delete All Blank Columns in Microsoft Excel

Every now and then, the data that you import from web pages may result in a great number of columns appearing even if they’re not used. You see this happening with CSV files and .txt files just as often.

How To Delete All Blank Columns in Microsoft Excel

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

Using VBA Macro

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 in 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 execute the macro.
  8. Input the appropriate work 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 the empty columns should be erased, and all filled columns should be next to each other.

Using Excel Tools to Delete Blank Columns

Obviously, Excel wouldn’t be such a powerhouse if it didn’t have great sorting abilities. You can use the Delete dropdown menu to delete entire rows, columns, or 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 targeted range).
  5. Go to the Home tab.
  6. Select the Delete dropdown menu under the Cells tools group.
  7. Select Delete Cells.
  8. Select Shift cells left for removing and rearranging columns.
  9. Click OK.

Now the empty cells from the blank columns should’ve disappeared, and all the other rows are closer 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 for removing and rearranging rows.

Depending on what version of Excel you’re running, you may get different wordings. 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 in the selection. Before Excel 2013, this would inadvertently remove even empty rows, which would generally mess up the sorting.

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

Deleting Blank Columns on Android

Although the process is similar for deleting blank columns in Excel on Windows, Mac, and Android, here’s a quick overview of doing it on Android.

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

Other Easy to Perform Sorting Tasks

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

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

You can also remove unnecessary comments or all comments from your project if you don’t want them to show up during your presentation. Look into VBA if you want to be an Excel power user.

A Final 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 the trouble for simple tasks such as removing blank rows, cells, and columns.

Besides, if it were that difficult, Microsoft would’ve further simplified the process by now or created even more extensive guides on Excel sorting.

One thought on “How To Delete All Blank Columns in Microsoft Excel”

Jose Torres says:
Use Advanced Filter. First Open a new sheet. Then copy only nonblank colums’ headings. (Give a distinctive formatting and adjust column width to leave some space at the right, i have noticed that tight headings may cause problems). Select a cell two or more rows below the headings.In Advanced Filter-Action, select “Copy to anorther location”. Click the selector in “List Range”, click your worksheet and select it all. Click the selector again to expand it. Do not write anything in “Criteria range”. Click the selector for “Copy to” and select the range for the headings you have just created above.. Expand the selector and press OK. Your blank columns disappear. Its much easier than it looks explained and you are at no risk of deleting columns that only contain some blank cells.. You can use this method to create smaller workkheets from worksheets with a lot of columns.

Leave a Reply

Your email address will not be published.


Disclaimer: Some pages on this site may include an affiliate link. This does not effect our editorial in any way.

Todays Highlights
How to See Google Search History
how to download photos from google photos