If you use Excel for something like Management Reports, you may need an easy way to hide columns based on cell value e.g. if you choose the month of February, all other months must be hidden. There is an easy macro you can build to achieve this.
Table of contents
YouTube Hide blank columns with simple macro
Formula to identify which columns to hide/ show
The first part is to create a formula (preferably in row 1 so that you know it won’t move around much) that calculates which columns should be hidden.
As shown below we have built a simple IF function that checks whether the month of the column matches the month in our dropdown list. You can use any criteria, the only thing that MUST happen is that the result, the one must be text (e.g. the word hide) and the other must be a number e.g. 1.
This is so that we can use one of Excel’s inbuilt tools to easily differentiate between which columns should remain visible and which should be hidden.
Record the macro
Next step is to start your macro recorder as shown below. This is going to record the next steps we show you which will then replay every time you run the macro.
You just need to name the macro and make sure it is set to ‘Store macro in- This workbook’
Create the steps to highlight the columns to be hidden
Keep in mind that the macro is recording. Now highlight the cells with the formula in (the IF function mentioned above which either shows a word or a number).
Click on HOME, then FIND & SELECT, then GO TO SPECIAL as shown below
Choose the Formulas and only leave the ‘Text’ ticked as shown below. This is telling Excel, that within the highlighted cells, only select the cells that
- are a formula (starts with =)
- end up with a result which is text
Hide all columns related to the highlighted cells
With the resultant cells highlighted (in our example all the cells that contain the text ‘Hide’ in the cells):
- Go to the HOME ribbon
- Choose the FORMAT button
- Click on the Hide & Unhide button
- Choose ‘Hide Columns’
Stop the recorder as you are now done.
We would also suggest you record another macro that unhides all the columns
Attach Hide Column macro to a button
To make it easier to use we suggest you put hide and unhide buttons on the sheet and attach the macros. You can just right click on the button and click the Assign Macro option to
- attach the Hide column macro to the Hide button and the
- Unhide all columns macro to the Unhide button.
Now when you make a change to the dropdown and push the Hide button, the steps Excel will follow will be:
- Change row 1’s answers so that all columns that match the selected month have a 1 and the others have ‘text’
- Macro will highlight all the cells in row 1 that are text
- The columns of these highlighted cells will be hidden.
Related
Auto hide/ unhide columns in Excel (without VBA)