VBA Hide Columns

Excel VBA Hide Columns

Hiding and unhiding columns in excel is the simplest task to do. And automating this process is even easy to do. VBA Hide Column does not need huge code to write. But before we move ahead in writing the code for hiding the column in VBA, we need to understand a few things and requirement. We need to know the column name or sequence which we want to hide and the range of the cell which is an optional thing.

How to Hide Columns in Excel VBA?

We will learn how to hide a column in VBA with a few examples in Excel.

Excel VBA Hide Column – Example #1

For this, we need data in the form of multiple filled columns. Here we have a data set where we have randomly taken the numbers starting from 1 to 40 in different 4 columns A, B, C and D respectively and all columns are colored in different colors just to have a better visual on the webpage.

Suppose, if we want to hide Column A and B then first we need to select both the columns as shown below.

Now, right-click and select Hide option as shown below.

The selected columns will get hidden after that. We could only see column C and D with data.

Now if similar work can be done by VBA macro as well. For this, we would need a module. So go to VBA and select a Module from Insert menu list as shown below.

In the newly opened Module, write the subcategory of VBA Hide Column or we can choose any other name as per our choice as shown below.

Code:

Sub Hide_Column()

End Sub

First, select the column/s that we need to hide. Here we are hiding column C.

Code:

Sub Hide_Column()

Range("C:C")

End Sub

As we want to hide entire Column C, select the EntireColumn function followed by Hidden as shown below.

Code:

Sub Hide_Column()

Range("C:C").EntireColumn.Hidden

End Sub

Now, to make this happen, we can choose answer TRUE or FALSE. If we choose Hidden as TRUE then, it would as hiding function or if we choose FALSE then it would work as unhiding function. As we want to hide the selected columns C from the range, so we would select TRUE here.

Code:

Sub Hide_Column()

Range("C:C").EntireColumn.Hidden = True

End Sub

Now compile the code and run it by clicking on the Play button located below the menu bar in VBA. We will see, column C is now hidden, now it only shows columns A, B, and D.

If we want to hide more than one column together. Then in the range, we need to select those columns as shown below. Here, we are selecting columns B and C.

Code:

Sub Hide_Column()

Range("B:C").EntireColumn.Hidden = True

End Sub

Now again run the code.

Here we can see, column B and C are hidden now. And only column A and D are visible.

Excel VBA Hide Column – Example #2

Instead of selecting the columns by mentioning the name, we can hide any column by their sequence as well. This process many times don’t get preferred because we can easily get to know the name of the column at which alphabetical location the column is located. This process is similar to the code which we have seen in example #1, where we have selected the column name as B and B:C, we will select the sequence number at which the column is located. If suppose we want to hide the column A using the code of example-1, we just need to place the sequence number instead of selecting it with the help of Range.

Code:

Sub Hide_Column2()

Columns(1).EntireColumn.Hidden = True

End Sub

Now, run this code by pressing the F5 key or by clicking on the Play button. We will see, column A which is at the first position is now hidden.

Excel VBA Hide Column – Example #3

There is another way to hide the column using VBA code. This is also as easy as the code which we have seen in example #1. For this again we will use the same data that we used in the above example. Write the subcategory in any name or in the name of a performed function.

Code:

Sub Hide_Column3()

End Sub

Select any range of column that we need to hide. Here, we are selecting the same column C.

Code:

Sub Hide_Column3()

Range("C:C")

End Sub

To activate the selection of required column, select Columns function as shown below.

Code:

Sub Hide_Column3()

Range("C:C").Columns

End Sub

Then use Hidden command, to activate hide and unhide function in VBA.

Code:

Sub Hide_Column3()

Range("C:C").Columns.Hidden

End Sub

And select TRUE for hiding the selected column and FALSE for unhiding the selected hidden column.

Code:

Sub Hide_Column3()

Range("C:C").Columns.Hidden = True

End Sub

Column C which was our range column is now hidden.

Excel VBA Hide Column – Example #4

There is another simplest way to automate the process of hiding columns in VBA. For this again write the subcategory in the name of VBA Hide Columns.

Code:

Sub Hide_Column4()

End Sub

Directly select the column that we need to hide. Here again, we are selecting the same column C.

Code:

Sub Hide_Column4()

Columns("C")

End Sub

And select the Hidden command as TRUE to activate the hiding process. Or select FALSE to activate the unhiding process.

Code:

Sub Hide_Column4()

Columns("C").Hidden = True

End Sub

We will see, column C is again hidden.

Pros & Cons of Excel VBA Hide Column

• The process of VBA Hide Column is very easy to implement.
• We can hide any type of column Range whether it is in sequence or in random positions.
• As the process of VBA Hide Column is easy to apply but in manually, it also as easy as doing it by VBA code. So, it does not contribute much in saving. Highly recommended only when we are working on huge code writing.

Things to Remember

• We hide and unhide the column with the help of some VBA code. For unhiding, use FALSE instead of TRUE for functioning purpose.
• Column sequence number also works for hiding the column apart from selecting the column name which is mentioned alphabetically.

Recommended Articles

This is a guide to VBA Hide Columns. Here we discuss how to hide columns in Excel using VBA Code along with practical examples and downloadable excel template. You can also go through our other suggested articles –

1. VBA Worksheets
2. VBA UserForm
3. VBA with
4. VBA AND