Introduction

One of the most common operations when using VBA is to hide columns to declutter a worksheet. Maybe you have tons of data and all the information isn't necessary to show to the user. Maybe some of the data is intermediate data and all you want to show is the final results. This is a very common use case and we'll show you how to do it. Let's jump straight into how to use the Excel VBA hide column functionality.

The Excel VBA Hide Columns

One of the easiest things to do in excel is hiding a column or row but the first thing we need to do is to identify the correct column name we want to hide and Range method will assist us in finding that specific column.

Different Options in Hiding Columns

We have four (4) different kinds of properties or methods that we can use in hiding different cells. The first one is the Range Object itself, wherein we use this code: Range("A:A"). The second one is the Column Property wherein we add this code: entire column. The third one is the Hidden property. And lastly, setting the Hidden property to true.

Let us use the demo data below to demonstrate using the different options:

Range Object Method

In this first demo we will be hiding Column A using the range object method.

See the code below:

Sub Example1()  

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

End Sub  

Let's take a look at the result after running the code:

Columns Property

We have three different codes that can be used in hiding by columns property. See different codes below:

• Code number 1:

Sub Example2()  

Columns("B").Hidden = True  

End Sub  

The result will be:

• Code number 2:

Sub Example2()  

Columns("B").EntireColumn.Hidden = True  

End Sub  

The result will be:

• Code number 3:

In the below code, we used the number of columns like the below code, we used columns(2) as column B because the column is second in the worksheet's column.

Sub Example2()  

Columns(2).EntireColumn.Hidden = True  

End Sub  

Let's take a look at the result:

Hiding Multiple Columns

We can also hide multiple columns with just one line of code. We just need to designate the first column and the last column you wish to be hidden. See the scripts below:

• Script number 1:

Sub Example3()  

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

End Sub  

The result after running the script:

• Script number 2:

Sub Example3()  

Columns("B:D").EntireColumn.Hidden = True  

End Sub  

The result of the script:

Using a Single Cell in Hiding a Column

We can also hide an entire column even if we only identify a single cell.

See the script below:

``
Sub Example4()

Range("C5").EntireColumn.Hidden = True

End Sub
``

By designating just a cell and using the EntireColumn property we can hide the entire column.

Hiding Alternate Columns

One common use case is to hide every other column. See the script below for how we might accomplish that:

Sub Example5()  

Dim i As Integer  

For i = 1 To 7  
Cells(1, i + 1).EntireColumn.Hidden = True  
i = i + 1  
Next i  

End Sub  

The result after running the code:

We have successfully hidden alternate columns using loops.

Hiding Empty Columns

Now let's take a look at hiding empty columns. Below is the data reference modified to have some empty columns.

We can hide those empty column using the script below:

Sub Example6()  

Dim i As Integer  

For i = 1 To 7  
If Cells(1, i).Value = "" Then  
Columns(i).Hidden = True  
End If  
Next i  

End Sub  

Conclusion

In this article we demonstrated several different methods of hiding columns in Excel using VBA. We have shown you how you might hide multiple columns as well as hide alternating columns using loops. We hope this tutorial will help you build smarter Excel worksheets.