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.
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, in the Go To Special dialog box, check the Blanks option. See screenshot:
3. And then click OK, all of the blank columns of the selected range are highlighted. See screenshot:
4. Then click Home > Delete > Delete Sheet Columns, the blank rows have been deleted from the selected range. See screenshot:
If you'd like to use VBA macro, things goes much easier. The following VBA code may help you, please do as this:
1. Hold down the Alt + F11 keys in Excel, and it opens the Microsoft Visual Basic for Applications window.
2. Click Insert > Module, and paste the following macro in the Module Window.
VBA code: Delete multiple blank columns in selection:
Sub DeleteEmptyColumns() 'Updateby Extendoffice 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
3. Press the F5 key to run this macro. Select the work range you need to the pop out dialog. See screenshot:
4. Click OK, then all empty columns in the selection are removed. See screenshot:
The Delete Hidden (Visible) 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 300 handy Excel add-ins, free to try with no limitation in 30 days.|
After installing Kutools for Excel, please do as this:
1. Click Kutools > Delete > Delete Hidden (Visible) Rows & Columns, see screenshot:
2. In the Delete Hidden (Visible) 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:
3. Then click OK. And the blank columns have been deleted from the selected range. See screenshots:
To know more about this feature, please visit the Delete Rows & Columns function.
You are guest ( Sign Up? )
or post as a guest, but your post won't be published automatically.
- To post as a guest, your comment is unpublished.· 9 months agoIt deleted all my names in my first name column! Auuggh!
- To post as a guest, your comment is unpublished.· 2 years agoThanks its very helpful page with basic excel option :)
- To post as a guest, your comment is unpublished.· 3 years agoHi,
can anyone guide how i can delete blank cells in a row or column...note whole blank column or rows... only blank cell i need to delete in a column
- To post as a guest, your comment is unpublished.· 2 years agoHello,
try to implement below code
- To post as a guest, your comment is unpublished.· 4 years agoHi, the thing which you are mentioned is so helpfull.
I want a small help from your side.
Daily am download the some files and using pivot prepared the reports. It is daily task for me and routine process. How can i do it with out using pivot.
Ex: If i prepared the reports on yesterday in "A" Excel file.
Next day in "A" Excel file just i replace the new "B" Excel file data. By this automatically report would be create?
Is it possible?
- To post as a guest, your comment is unpublished.· 5 years agoVery helpful page. Thank you. :-)
My challenge is that I have columns with headers, but no data in that column. Only the header is present. How would one go about deleting columns where there is no data, but the header is there? :o
- To post as a guest, your comment is unpublished.· 6 years agothe above details are very helpful for my personal use. Thanks for your guidance.
- To post as a guest, your comment is unpublished.· 6 years agoI found it much simpler to go to the line after my last line of data, enter "Shift-Ctrl-End" to highlight all blank lines that followed(much faster), right click and enter "delete all rows/columns". The system didn't waste so much time searching this way and so it was clean in the blink of an eye and files that were 20 MB were dropped to a size of 20 kb.