We are not great fans of using VBA where we can think of another way. One of those areas we think can be addressed in other ways is automatically hiding and unhiding columns in Excel depending on how many columns you want the user to see. Below an option to auto hide/ unhide columns in Excel without VBA (by making them fade into the background).
We have also included how we would use simple macros to either hide columns or rows based on certain criteria (they are blank, unhide the current month etc).
Table of contents
YouTube Hide Columns based on cell value (simple macro)
YouTube Hiding/ unhiding rows with simple macro
We have shown a way to hide/ unhide rows with the Filter (we added a small recorded macro to make it a bit easier). Below the YouTube video clip for hiding unused/ blank rows.
Auto hide/ unhide columns in Excel without VBA
The problem with columns is that the Filter does not work that way. So an alternate is to make it look like it is hidden/ empty when it is actually there. For this, conditional formatting is a great tool.
In the example below, we have a simple debt amortisation schedule. We have built for 10 years, but depending on the users selection it could be any period of time. We don’t want to see all the extra columns after the debt is repaid.
An easy (and safe) way to achieve this is to effectively hide these extra columns by changing the cell format so that the font colour is white.
Notice that in row 7 I have created a trigger row which simply checks if the column has gone past the last year of the loan (IF function that looks at the years in row 9).
Now we can use conditional formatting to hide these extra columns. As shown below, you need to highlight ALL the cells that may be hidden, click on HOME, CONDITIONAL FORMAT, NEW RULE and choose the ‘Use a formula’ option.
It is important to realise that when you build the formula, you must pretend you are only thinking about the active cell which in this case is B9 (the one white cell in the grey section). Notice that the formula we built tells cell B9, to look at B$7 and see if it is a 1. The $ sign is important because when Excel gets to row 10, 11 etc it will want to look at other rows, but row 7 is our trigger row.
You can now click format and change the font colour to be white (or whatever colour your background is) and for extra you can remove the border so that it looks like it disappears.
We have left the trigger row so that you can see it, but you can apply the same logic to that row or just hide the row.
Now when a user changes the loan period from 6 to 3 years, it looks like the columns automatically hide themselves.
And if you wanted to change how much to see (perhaps you want to see 1 year after the loan expiry) you just need to change the formula in the trigger row 7 and conditional formatting will do the rest with no adjusting of code.