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.

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.

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 True or False. You don’t call .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 True:

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 False unhides the column. If your column number can change, you might find out VBA column number to letter function useful for converting column numbers to letters.

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.

I'll take a free VBA Developer Kit

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 a and b in this example, especially when they were set 500 lines earlier:

Sub crypticToggleHiddenWithNot()
a.Hidden = Not b
End Sub

The variable a could be a column on the active sheet or it could be a row in another workbook. The variable b is definitely a Boolean, but who knows where it came from. These kinds of instances require comments (or, better yet, more verbosity).

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.