Spreadsheets can have a lot of information stored on them that users do not need to see, like helper columns. It’s often better to hide this information automatically so users don’t accidentally make any changes. 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.
- The Column Object and its .Hidden Property
- Hiding Multiple Columns
- Toggle Hidden Columns
The Column Object and its .Hidden Property
In VBA, the
Column object accesses a specific column in a
Sheet object. It’s important to recognize that Column objects reside on specific sheets. The expression
Columns(1) referenecs different columns on different sheets, just like
Range("A1") references a different cell on different sheets. Column A on sheet Sales is obviously different than Column A on sheet Customers.
Thus, when making a call on a
Column object, you must either specify the sheet or expect the currently active sheet to be used. Again, this is no different than working with VBA Range objects.
The .Hidden Property
Every Column object has a
.Hidden property, which is of type
Boolean. This property can only be TRUE or FALSE. Note that this is a property, not a method. This means you must set it equal to something, which in this case would be
.Hide() to make the column hidden. Similarly, you also don’t call
.Unhide() to make the column reappear.
If we think about this for a second, it makes sense to implement hidden-ness as a property instead of as a method. If a column is already hidden and we were to call
Hide() on it, it would not do anything. Moreover, there would be no way to check whether any specific column is hidden or not. We would only ever be able to blindly hide and unhide columns.
So, to hide a column, simply set its
.Hidden property to
Columns(1).Hidden = True
If you want to use letter notation, you can do so with quotation marks:
Columns("A").Hidden = True
There’s nothing more to it. As you might expect, setting the property to
Hiding Multiple Columns with VBA
Hiding more than one column requires a different approach. If you follow our site, you’ll know that a
Column object refers to a single instance while the plural
Columns will refer to a collection. If you don’t follow our posts, you should consider it:
Make powerful macros with our free VBA Developer Kit Tutorials like this can be complicated. That’s why we created our free VBA Developer Kit and our Big Book of Excel VBA Macros to supplement this tutorial. Grab them below and you’ll be writing powerful macros in no time.
Tutorials like this can be complicated. That’s why we created our free VBA Developer Kit and our Big Book of Excel VBA Macros to supplement this tutorial. Grab them below and you’ll be writing powerful macros in no time.
In VBA, there isn’t a
Column object exposed to the programmer, but
Columns(1) references a single Column object - the first one - in the
Columns collection. To reference several columns at once, you must group them together in a range:
Range(Columns(1), Columns(3)).Hidden = True
This macro hides columns 1, 2, and 3.
If you know the letter, you could also hide Columns A, B, and C with quotations instead of grouping them into a range:
Columns("A:C").Hidden = True
Toggle Hidden Columns
With VBA, it’s easy to check if columns are hidden it’s governed by a property of the
Column object. Because this property is Boolean, you can toggle it with the
Not modifier. To the user, it’s obvious that columns are hidden or not, and they will want a single button to hide/unhide rather than two buttons (one to hide and a separate one to unhide).
Compare these two ways to toggle hidden columns, perhaps connected to a button that a user can press. One uses
Not to toggle, and the other complicates the issue with if statements:
Sub toggleHiddenWithIfs() If Columns("A:C").Hidden = True Then Columns("A:C").Hidden = False Else Columns("A:C").Hidden = True End If End Sub
Sub toggleHiddenWithNot() Columns("A:C").Hidden = Not Columns("A:C").Hidden End Sub
Both macros work, but the second one is much cleaner. If you are learning to program, I recommend trying to use logic like in the second macro rather than brute-forcing it with explicit If statements (i.e., checking the possible conditions and acting accordingly). Short, elegant code makes the macro cleaner and more readable. In the example above, there isn’t a problem with the code, but when you have thousands of lines of code, being overly explicit and testing cases can lead to bloat.
Of course, be judicious. If your code becomes confusing because of your cryptic logic, it might be better to make the code more verbose - or at least use comments (VBA comments are declared using an apostrophe
'.). No one wants to hunt down
Sub crypticToggleHiddenWithNot() a.Hidden = Not b End Sub
Hiding and unhiding columns is rather straightforward. Perhaps the only slightly non-intuitive part is that we set the propery of
.Hidden on the column objects rather than using a method like
.Hide() to accomplish the goal.
Hiding and unhiding columns with VBA can really help your users visualize what’s going on in complex spreadsheets. This is especially true if the results column is far from the input column with several intermediate “helper” columns. 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. It might be better to hide columns B through M using your macro, unless someone needs to edit those fields. That’s when a VBA hide/unhide toggle button, like the one we showed earlier, might come in handy.
When you’re ready to take your VBA to the next level, subscribe using the form below.