How to delete multiple empty columns quickly in Excel?

Sometimes when you import data to Microsoft Excel from some source, such as web page, CSV, text, etc., there may be many empty columns with it. It must be time-consuming to delete each empty column one by one. Therefore, some of us search for easy ways to solve this problem. This article collects some tricky tips to help you delete multiple empty columns quickly.

Delete multiple empty columns with Go To Special Command

Delete multiple empty columns with VBA code

Delete multiple empty columns with Kutools for Excel

Recommended Productivity Software

Office Tab: Use tabbed interface in Office as the use of web browser Chrome, Firefox and Internet Explorer.
Kutools for Excel: Adds 120 powerful new features to Excel. Increase your productivity in 5 minutes. Save two hours every day!
Classic Menu for Office: Brings back your familiar menus to Office 2007, 2010 and 2013 (includes Office 365).

arrow blue right bubble Delete multiple empty columns with Go To Special Command

Hint


The Excel’s Go To Special Command can help you quickly select the multiple blank columns, and then you can delete them at once. You can do as the following steps:

1. Select the range containing the blank columns that you want to remove.

2. Click Home > Find & Select > Go To Special. See screenshot:

3. In the Go To Special dialog box, check the Blanks option.

4. And then click OK, all of the blank columns of the selected range are highlighted.

5. Then click Home > Delete > Delete Sheet Columns, the blank rows have been deleted from the selected range. See screenshots:

-1


arrow blue right bubble Delete multiple empty columns with VBA

If you'd like to use VBA macro, things goes much easier. Here are two VBA macros for you.

1. Delete empty columns in selection

Step 1: Select the range that you will work with.

Step 2: Hold down the Alt + F11 keys in Excel, and it opens the Microsoft Visual Basic for Applications window.

Step 3: Click Insert > Module, and paste the following macro in the Module Window.

VBA for deleting columns in selection

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

Step 4: Press the F5 key to run this macro. Select the work range you need to the pop out dialog. See screenshot:

Step 5: Click OK, then all empty columns in the selection are removed. See screenshot:

2. Delete empty columns in the whole using working area

Step 1: Hold down the Alt + F11 keys in Excel, and it opens the Microsoft Visual Basic for Applications window.

Step 2: Click Insert > Module, and paste the following macro in the Module Window.

Sub DelEmptyCols()
'Updateby20140317
Dim rng As Range
xTitleId = "KutoolsforExcel"
Set InputRng = Application.ActiveSheet.UsedRange
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

Step 3: Press the F5 key to run this macro. Then all columns in the active worksheet are removed.


arrow blue right bubble Delete multiple empty columns with Kutools for Excel

The Delete Rows & Columns utility of Kutools for Excel can help you quickly remove multiple empty rows or columns at once.

Kutools for Excel: with more than 120 handy Excel add-ins, free to try with no limitation in 30days. Get it Now.

After installing Kutools for Excel, please do as this:

1. Click Kutools > Delete Tools > Delete Rows & Columns, see screenshot:

doc-delete-blank-columns5

2. In the Delete Rows & Columns dialog box, specify the scope that you want to apply the operation from Look in drop down list. (If you choose In Selected Range, you must select a range that you want to use first.) And then check Columns under Delete type. And select Blank columns from Detailed type. See screenshot:

doc-delete-blank-columns6

3. Then click OK. And the blank columns have been deleted from the selected range. See screenshots:

doc-delete-blank-columns7
-1
doc-delete-blank-columns8

To know more about this feature, please visit the Delete Rows & Columns function.


Related articles:


Is your problem solved?

Recommended Productivity Tools

The following tools will greatly save your time and effort, which one do you prefer?
Office Tab: Using handy tabs in your Office, as the way of Chrome, Firefox and New Internet Explorer.
Kutools for Excel: 120 powerful new functions for Excel, Increase your productivity in 5 minutes. Save two hours every day!
Classic Menu for Office: Bring back familiar menus to Office 2007, 2010, 2013 and 365, as if it were Office 2000 and 2003.

Kutools for Excel

gold star1 Amazing! Increase your productivity in 5 minutes. Don't need any special skills, save two hours every day!

gold star1 More than 120 powerful advanced functions which designed for Excel:

  • Merge Cell/Rows/Columns without Losing Data.
  • Combine and Consolidate Multiple Sheets and Workbooks.
  • Compare Ranges, Copy Multiple Ranges, Convert Text to Date, Unit and Currency Conversion.
  • Count by Colors, Paging Subtotals, Advanced Sort and Super Filter,
  • More Select/Insert/Delete/Text/Format/Link/Comment/Workbooks/Worksheets Tools...

Screen shot of Kutools for Excel

btn read more     btn download     btn purchase