#HIDE COLUMNS IN EXCEL HOW TO#
#HIDE COLUMNS IN EXCEL CODE#
#HIDE COLUMNS IN EXCEL DOWNLOAD#

Now the formula explicitly indicates which columns are empty and which are not: No problem, this can be easily done with an IF statement similar to this: In case you are setting up the worksheet for someone else, you may want to label the columns in a more meaningful manner. Due to the use of relative cell references, the formula properly adjusts for each column where it is copied. You compare that number with zero and, as the result, have TRUE in blank columns and FALSE in the columns that contain at least one non-empty cell. 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. In the leftmost cell of the newly added row, enter the following formula:Īnd then, copy the formula to the other columns by dragging the fill handle. Do not worry about mangling the structure/arrangement of your data - you can delete this row later. 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 In the pop-up dialog, select the range and click OK.Įither way, all empty columns in the selected range will be disposed of:.In your workbook, press Alt + F8, select the DeleteEmpt圜olumns macro, and click Run.Open your own workbook or switch to the already opened one.

#HIDE COLUMNS IN EXCEL DOWNLOAD#

Download our sample workbook to Remove Blank Columns in Excel, open it, and enable content if prompted.

If you do not want to add a macro to your worksheet, you can run it from our sample workbook.

When the pop-up dialog appears, switch to the worksheet of interest, select the desired range, and click OK:.

#HIDE COLUMNS IN EXCEL CODE#

Paste the above code in the Code window.

On the menu bar, click Insert > Module.

Press Alt + F11 to open the Visual Basic Editor.

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

#HIDE COLUMNS IN EXCEL HOW TO#

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

One-click way to remove empty columns in Excel.

Find and delete blank columns with a formula.

How NOT to delete empty columns in Excel.

Luckily, Microsoft Excel provides a great lot of different features, and by using those features in creative ways you can cope with almost any task! The prospect of reviewing all the columns in your worksheet and removing the empty ones manually is definitely something you'd want to avoid.

The tutorial will teach you how to remove empty columns in Excel with a macro, formula and a button-click.Īs trivial as it sounds, deleting empty columns in Excel is not something that can be accomplished with a mere mouse click.

Hide columns in excel