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.
Hiding/ unhiding rows
We have shown a way to hide/ unhide rows with the Filter (we added a small recorded macro to make it a bit easier). You can see how on the Hide VBA Macro.
Auto Hide/ Unhide columns in Excel
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.