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.
- Go to your Excel file.
- Delay Alt + F11 together.
- Wait for the Microsoft Visual Basic for Applications window to appear.
- Then click Insert.
- Select module.
- Paste the following lines of code into the window.
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
Application.ScreenUpdating = True
- Press F5 to compile and run the macro.
- Enter the correct working range in the dialog box.
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.
- Press Okay.
After that, all empty columns must be cleared and all filled columns must be next to each other.
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.
- First select the data range and press F5.
- Then click Special.
- Now select the Blanks option.
- Click OK (this selection will select all blank cells in the target range).
- Go to the House tab.
- Select the remove drop-down menu under the Cell Tools group.
- Select Delete cells.
- Select Shift cells to the left for deleting and rearranging columns.
- Click Okay.
Now the empty cells should be gone from the empty columns 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, select the other option.
Select Slide cells up for deleting and rearranging rows.
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.
- Open the Excel spreadsheet you want to edit and tap the column header you want to edit.
- 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.