How To Delete Columns From Excel Templates

The tutorial volition teach you how to remove empty columns in Excel with a macro, formula and a button-click.

As piffling as it sounds, deleting empty columns in Excel is non something that tin exist accomplished with a mere mouse click. Information technology cannot exist done in two clicks either. The prospect of reviewing all the columns in your worksheet and removing the empty ones manually is definitely something you'd want to avert. Luckily, Microsoft Excel provides a great lot of dissimilar features, and by using those features in creative ways you lot tin cope with almost any task!

How NOT to delete empty columns in Excel
Remove blank columns with VBA
Find and delete blank columns with a formula
One-click fashion to remove empty columns in Excel
Deleting blank columns does not work

Quick way to delete empty columns that y'all should never utilise

When it comes to removing blanks in Excel (whether information technology is empty cells, rows or columns), many online resources rely on the Go to Special > Blanks command. Never practice that in your worksheets!

This method (F5 > Special… > Blanks) finds and selects all empty cells in the range:

If at present you right-click the selected cells and choose Delete > Unabridged column, all the columns that comprise at to the lowest degree 1 blank jail cell would be lost! If you've inadvertently did that, printing Ctrl + Z to get everything dorsum.

Now that you know a wrong style to delete blank columns in Excel, let's see how to do it right.

How to remove bare columns in Excel with VBA

Experienced Excel users know this rule of a thumb: not to waste material hours doing something manually, invest a few minutes in writing a macro that will do it for you automatically.

The below VBA macro removes all bare columns in the selected range. And information technology does this safely - only absolutely empty columns are deleted. If a column contains a unmarried cell value, even an empty string returned by some formula, such a column volition remain intact.

Public Sub DeleteEmptyColumns() 	Dim SourceRange Equally Range 	Dim EntireColumn Equally Range  	On Error Resume Side by side  	Set up SourceRange = Application.InputBox( _ 		"Select a range:", "Delete Empty Columns", _ 		Application.Option.Address, Type:=8)  	If Not (SourceRange Is Nothing) Then 		Awarding.ScreenUpdating = False  		For i = SourceRange.Columns.Count To 1 Step -ane 			Set EntireColumn = SourceRange.Cells(1, i).EntireColumn 			If Awarding.WorksheetFunction.CountA(EntireColumn) = 0 Then 				EntireColumn.Delete 			End If 		Next  		Awarding.ScreenUpdating = True 	Terminate If Finish Sub

How to use the Delete Empty Columns macro

Here are the steps to add the macro to your Excel:

Press Alt + F11 to open the Visual Basic Editor.
On the carte bar, click Insert > Module.
Paste the higher up lawmaking in the Code window.
Press F5 to run the macro.
When the pop-up dialog appears, switch to the worksheet of interest, select the desired range, and click OK:

If you do not want to add together a macro to your worksheet, yous tin can run information technology from our sample workbook. Hither'due south how:

Download our sample workbook to Remove Blank Columns in Excel, open up it, and enable content if prompted.
Open your own workbook or switch to the already opened ane.
In your workbook, printing Alt + F8, select the DeleteEmptyColumns macro, and click Run.
In the pop-upward dialog, select the range and click OK.

Either way, all empty columns in the selected range volition be disposed of:

Identify and delete blank columns in Excel with a formula

The above macro removes empty columns quickly and silently. Merely if you lot are a "keep-everything-nether-control" kind of person (like I am :) y'all may want to visually encounter the columns that are going to be removed. In this case, nosotros volition offset identify blank columns by using a formula and so that you could quickly review them, so eliminate all or some of those columns.

Note. Before deleting anything permanently, peculiarly by using an unknown technique, I strongly advise you lot to make a backup copy of your workbook, merely to be on the safe side if something goes incorrect.

With a backup copy in a condom place, perform the post-obit steps:

Step 1. Insert a new row

Add together a new row at the height of your tabular array. For this, right-click the outset row header and click Insert. Do not worry about mangling the structure/organisation of your data - you can delete this row after.

Stride 2. Place empty columns

In the leftmost cell of the newly added row, enter the following formula:

=COUNTA(A2:A1048576)=0

And and so, copy the formula to the other columns by dragging the make full handle.

The formula's logic is very simple: COUNTA checks the number of blanks cells in the column, from row 2 to row 1048576, which is a row maximum in Excel 2019 - 2007. You compare that number with zero and, as the issue, have Truthful in blank columns and Simulated in the columns that contain at to the lowest degree ane not-empty prison cell. Due to the use of relative prison cell references, the formula properly adjusts for each column where it is copied.

In case y'all are setting up the worksheet for someone else, yous may want to label the columns in a more meaningful manner. No problem, this can be hands done with an IF statement similar to this:

=IF(COUNTA(A2:A1048576)=0, "Blank", "Not blank")

Now the formula explicitly indicates which columns are empty and which are not:

Tip. Compared to a macro, this method gives you more flexibility with regard to which columns should exist considered blank. In this example, we check the whole tabular array, including the header row. That ways if a column contains only a header, such a cavalcade is non regarded as bare and is not deleted. If you lot'd like to bank check only data rows ignoring cavalcade headers, remove the header row(southward) from the target range (A3:A1048576). As the result, a cavalcade that has a header and no other data in it volition be deemed bare and subject to deleting. Also, you can limit the range to the final used row, which would be A11 in our case.

Step 3. Remove blank columns

Having a reasonable number of columns, y'all can simply select those that accept "Blank" in the first row (to select multiple columns, concord the Ctrl key as you click the column messages). Then, right-click whatever selected column, and choose Delete from the context carte du jour:

If there are tens or hundreds of columns in your worksheet, it makes sense to bring all empty ones to view. For this, do the following:

Select the top row with formulas, go to the Data tab > Sort and Filter group, and click the Sort push button.
In the alert dialog box that appears, select Expand the selection, and click Sort…
This will open the Sort dialog box, where you click the Options… button, select Sort left to correct, and click OK.
Configure just 1 sort level similar shown below and click OK:
- Sort by: Row one
- Sort On: Prison cell Values
- Order: A to Z
As the result, the blank columns will be moved to the left part of your worksheet:
Select all bare columns - click on the first cavalcade letter, press Shift, and then click the letter of the final blank cavalcade.
Correct-click the selected columns and choose Delete from the pop-upward card.

Done! Y'all've got rid of the blank columns, and there is nothing that would now prevent you from deleting the top row with the formulas.

Fastest way to remove empty columns in Excel

In the commencement of this tutorial, I wrote that there is no one-click manner to delete blank columns in Excel. In fact, that isn't exactly true. I should accept said at that place is no inbuilt way. The users of our Ultimate Suite can remove blanks in Excel literally in a couple of clicks :)

In the target worksheet, switch to the Ablebits Tools tab, click Delete Blanks and cull Empty Columns:

To brand certain that wasn't an accidental mouse click, the add-in will ask you to confirm that y'all really want to remove empty columns from that worksheet:

Click OK, and in a moment all blank columns are gone!

Similar the macro discussed above, this tool deletes only those columns that are admittedly empty. Columns that have any single value, including headers, are preserved.

Delete Blanks is just ane of tens of wonderful features that tin brand your life as an Excel user easier. To observe more, you are welcome to download a trial version of our Ultimate Suite for Excel.

Blank columns are not deleted! Why?

Upshot: You take tried all of the above methods, but one or more than empty columns are stuck in your worksheet. Why?

Nearly likely because those columns are non really empty. Many different characters invisible to the man centre may lurk unnoticed in your Excel spreadsheets, especially if you imported information from an external source. That can be a mere empty string or a space character, non-breaking space or some other non-press character.

To pin downward the culprit, select the first jail cell in the problematic column and press Ctrl + downwardly arrow. For example, column C in the screenshot below is not blank because of a unmarried space character in C6:

Double-click the jail cell to encounter what actually is in it or only printing the Delete key to get rid of the unknown something. Then repeat the above process to find out if there are whatever other invisible things in that column. You may also want to make clean your data by removing leading, trailing and not-breaking spaces.

I thank you for reading and promise to meet you on our blog next week!