For this, right-click the first row header and click Insert. Insert a new rowĪdd a new row at the top of your table. With a backup copy in a safe place, perform the following steps: Step 1. Before deleting anything permanently, especially by using an unknown technique, I strongly advise you to make a backup copy of your workbook, just to be on the safe side if something goes wrong. In this example, we will first identify blank columns by using a formula so that you could quickly review them, and then eliminate all or some of those columns. But if you are a "keep-everything-under-control" kind of person (like I am :) you may want to visually see the columns that are going to be removed. The above macro removes empty columns quickly and silently. Identify and delete blank columns in Excel with a formula
Here are the steps to add the macro to your Excel:
How to use the Delete Empty Columns macro Set EntireColumn = SourceRange.Cells(1, i).EntireColumn "Select a range:", "Delete Empty Columns", _įor i = To 1 Step -1 Set SourceRange = Application.InputBox( _ If a column contains a single cell value, even an empty string returned by some formula, such a column will remain intact. And it does this safely - only absolutely empty columns are deleted. The below VBA macro removes all blank columns in the selected range. How to remove blank columns in Excel with VBAĮxperienced Excel users know this rule of a thumb: not to waste hours doing something manually, invest a few minutes in writing a macro that will do it for you automatically. Now that you know a wrong way to delete blank columns in Excel, let's see how to do it right. If now you right-click the selected cells and choose Delete > Entire column, all the columns that contain at least one blank cell would be lost! If you've inadvertently did that, press Ctrl + Z to get everything back. This method ( F5 > Special... > Blanks) finds and selects all empty cells in the range: When it comes to removing blanks in Excel (whether it is empty cells, rows or columns), many online resources rely on the Go to Special > Blanks command. Quick way to delete empty columns that you should never use