That’s when a VBA hide/unhide toggle button, like the one we showed earlier, might come in handy. It might be better to hide columns B through M using your macro, unless someone needs to edit those fields. Imagine a user adding input to Column A and this input is combined with information in Columns B through M and you don’t get to the output until in Column N. This is especially true if the results column is far from the input column with several intermediate “helper” columns. Hiding and unhiding columns with VBA can really help your users visualize what’s going on in complex spreadsheets. Hidden on the column objects rather than using a method like. Perhaps the only slightly non-intuitive part is that we set the propery of. Hiding and unhiding columns is rather straightforward. These kinds of instances require comments (or, better yet, more verbosity). The variable b is definitely a Boolean, but who knows where it came from. The variable a could be a column on the active sheet or it could be a row in another workbook. We would only ever be able to blindly hide and unhide columns. Moreover, there would be no way to check whether any specific column is hidden or not. If a column is already hidden and we were to call Hide() on it, it would not do anything. If we think about this for a second, it makes sense to implement hidden-ness as a property instead of as a method. This means you must set it equal to something, which in this case would be True or False. Note that this is a property, not a method. Hidden property, which is of type Boolean. Again, this is no different than working with VBA Range objects.Įvery Column object has a. Thus, when making a call on a Column object, you must either specify the sheet or expect the currently active sheet to be used. Column A on sheet Sales is obviously different than Column A on sheet Customers. The expression Columns(1) referenecs different columns on different sheets, just like Range('A1') references a different cell on different sheets. It’s important to recognize that Column objects reside on specific sheets. In VBA, the Column object accesses a specific column in a Sheet object. Hiding and unhiding columns is very simple in VBA, so this tutorial will be less in-depth than some of our other ones, like the API one or the scraping one, which required many interdependent parts. It can also make the information easier to understand if columns are hidden, especially if there are several intermediate columns between the user-changeable columns and the final output columns. It’s often better to hide this information automatically so users don’t accidentally make any changes. Spreadsheets can have a lot of information stored on them that users do not need to see, like helper columns.

Hide columns in excel