How to Hide & Unhide Columns Based on Cell Values With VBA

How to Hide & Unhide Columns based on Cell Values with VBA

In this article, we will explain how to write VBA macro code to hide & unhide columns based on cell values. The cell value can be anything like text, numbers special characters or it can be a blank cell.

First type some values as numbers, text and bank cells as shown in the image above. You can see we have typed the values in row number 8 from column A to Z. Now we will create macro to hide and unhide number columns, text columns, blank cells and unhide all cells.

Hide or Unhide columns with numbers

Go to Developer tab, and click Visual Basic or You can press ALT+F11 to open the Visual Basic
Now click on Insert and then click Module.
Then write down the following code in module editor.

Now Save the file as Excel Macro-Enabled Workbook.
To check the code, click Developer tab and click on Macros on the Code
A Macro Dialog Box will appear.
In the Macro Name box select Number and click Run.
Now you can see all columns with values other than numbers got hidden.
N.B: You can also check the code by clicking the Run button of the Visual Basic editor.

Hide or Unhide columns with Text

Now, we will create another code to check all the cells of row number 8 from column A to Z. If the cell does not contains text then we will hide that column else keep it unhidden.

Go to Developer tab, and click Visual Basic or You can press ALT+F11 to open the Visual Basic
Now click on Insert and then click Module. Or you can also use the same module.
Then write down the following code in module editor.

Now Save the.
To check the code, click Developer tab and click on Macros on the Code
A Macro Dialog Box will appear.
In the Macro Name box select Number and click Run.
Now you can see all columns with values other than numbers got hidden.

N.B: You can also check the code by clicking the Run button of the Visual Basic editor.

Hide or Unhide columns with Blank cells

Now, we will once again check all the cells of row number from column A to Z. If the cell is not blank then we will hide that column else keep it unhidden. For that we will create a macro.

Go to Developer tab, and click Visual Basic or You can press ALT+F11 to open the Visual Basic
Now click on Insert and then click Module.
Then write down the following code in module editor.

Now Save the file as Excel Macro-Enabled Workbook.
To check the code, click Developer tab and click on Macros on the Code
A Macro Dialog Box will appear.
In the Macro Name box select Number and click Run.
Now you can see all columns with values other than numbers got hidden.

N.B: You can also check the code by clicking the Run button of the Visual Basic editor.

Unhide all Hidden Columns

First, we will check all the cells of row number from column A to Z. If the cell contains number then we will hide that column else keep it unhidden. For that we will create a macro.

Go to Developer tab, and click Visual Basic or You can press ALT+F11 to open the Visual Basic
Now click on Insert and then click Module.
Then write down the following code in module editor.

Now Save the.
To check the code, click Developer tab and click on Macros on the Code
A Macro Dialog Box will appear.
In the Macro Name box select Number and click Run.
Now you can see all columns with values other than numbers got hidden.

N.B: You can also check the code by clicking the Run button of the Visual Basic editor.

Auto Trigger all the Macros

Now we will create a code to trigger all these macros. So, for that we will use Worksheet Event, Go to Visual Basic.

In Project Explorer, double click on the sheet1. A new worksheet editor for shet1 will open.
Just above blank editor you can two dropdown list, on the first dropdown list select Worksheet instead of General, and on the second dropdown list, select Change.
Now type the following code

Now on cell AB8, type “Number” then all the columns except columns with numbers will get hidden. Similarly, you can type “Text” or “Blank”. If you want to unhide all column from A to Z then delete any values in cell AB8. You will see all the columns got unhidden.

That’s the end of this tutorial. I hope that this tutorial will be helpful for you to automate your Excel works. If you have any further queries you can write me in the comment section. You can also recommend any tutorial that I can create it for you.