WallStreetMojo

WallStreetMojo

WallStreetMojo

MENUMENU
  • Free Tutorials
  • Certification Courses
  • Excel VBA All in One Bundle
  • Login
Home » Excel, VBA & Power BI » Learn VBA » VBA Hide Columns

VBA Hide Columns

Excel VBA Hide Columns

Hiding is a simple thing, but you need to understand the concept here. To hide the column using VBA, we need to determine which column we need to hide. To specify the column to be hidden, we need to use the RANGE object.

Range("A:A")

Then we need to use the Property “Entire Column.”

Range("A:A").EntireColumn

In the entire column property, we need to use the “Hidden” property.

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

Then, at last, we need to set the Hidden property to TRUE.

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

This will hide column A.

How to Hide Columns in VBA?

We can hide columns in several ways. We can hide a column by using a Range object by using CELLS property. Based on the method we use, we need to construct our VBA code.

You can download this VBA Hide Columns Excel Template here – VBA Hide Columns Excel Template

Example #1 – Hide using Range Object

If you want to hide a particular column, then specify the column inside the RANGE object. Look at the below data.

vba hide column example 1.1

Now we will hide the entire column A using the following code.

Code:

Sub Range_Hide()

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

End Sub

vba hide column example 1.2

So when we run the code, we will get the following result.

vba hide 1

Example #2 – Hide using Columns Property

If you want to hide the column using Columns property, then the below code is for you.

Popular Course in this category
Sale
VBA Training (3 Courses, 12+ Projects)
4.6 (247 ratings)
3 Courses | 12 Hands-on Projects | 43+ Hours | Full Lifetime Access | Certificate of Completion
View Course

Code:

Sub Range_Hide()

    Columns("B").Hidden = True

End Sub

vba hide column example 2.1

This will hide column B. Here you must be thinking where the Entire Column property is. When you use Columns property, it is not necessary to use the Entire Column property.

We can still use the Entire Column property but not necessarily required. And code for the same is given below.

Code:

Sub Columns_Hide()

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

End Sub

vba hide column example 2.2

This also should work fine.

We can use the column number as well instead of alphabetic reference. And code for the same is given below.

Code:

Sub Columns_Hide()

    Columns(4).EntireColumn.Hidden = True

End Sub

vba hide column example 2.3

This will hide the Column D.

vba hide 2

Example #3 – Hide Multiple Columns

We can hide multiple columns at a time as well. We need to mention the first and the last column so that in between columns also will be hidden. For the same data as example #1, use the following code to hide multiple columns.

Code:

Sub Columns_Hide()

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

End Sub

vba hide column example 3.1

This will hide column A to C.

We can use the following code as well to hide multiple columns in Excel VBA.

Code:

Sub Multi_Columns_Hide()

    Columns("A:C").EntireColumn.Hidden = True

End Sub

vba hide column example 3.2

The above methods will hide the first three columns i.e., A, B, and C.

vba hide column example 3.3

Example #4 – Hide Columns with Single Cell

We can also hide a column based on a single cell reference. We don’t need to give the full column reference to hide the column. With just a single cell reference, we should be able to hide a column.

Code:

Sub Single_Hide()

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

End Sub

vba hide column example 5.3

This will hide the entire column A.

vba hide column example 4.2

Example #5 – Hide Every Alternative Column

Assume you have data, something like the below image.

vba hide column example 5.1

We need to hide every alternative column, which is blank. We need to use loops; the below code will do the job for us.

Code:

Sub AlternativeColumn_Hide()

    Dim k As Integer

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

End Sub

example 5.2

This will hide every alternate column.

5 - GIF

Example #6 – Hide Every Empty Column

In the previous example, every other alternative column was blank; we have hidden easily. But look at the below data.

example 6.1

Here empty columns pattern is not standard. In these cases, the below code will hide all the empty columns. It doesn’t matter what the pattern is.

Code:

Sub Column_Hide1()

    Dim k As Integer

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

End Sub

example 6.2

When you run the code, you will get the result as follows.

example 6.3

Example #7 – Hide Columns Based On Cell Value

Now we will see how to hide columns based on the cell value. For example, look at the below data.

example 7.1

Here I want to hide all the columns if the heading is “No.” The below code will do it for me.

Code:

Sub Column_Hide_Cell_Value()

    Dim k As Integer

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

End Sub

example 7.2

When you run the code, you will get the result as follows.

7 - GIF

Recommended Articles

This has been a guide to VBA Hide Columns. Here we discuss how to hide columns in VBA Excel and the practical examples and downloadable excel sheet. You can learn more about VBA from the following articles –

  • What is Intersect in Excel VBA?
  • Change Directory in VBA
  • VBA Collections
  • InputBox in VBA
0 Shares
Share
Tweet
Share
VBA Training (3 Courses, 12+ Projects)
  • 3 Courses
  • 12 Hands-on Projects
  • 43+ Hours
  • Full Lifetime Access
  • Certificate of Completion
LEARN MORE >>
Primary Sidebar
Footer
COMPANY
About
Reviews
Contact
Privacy
Terms of Service
RESOURCES
Blog
Free Courses
Free Tutorials
Investment Banking Tutorials
Financial Modeling Tutorials
Excel Tutorials
Accounting Tutorials
Financial Statement Analysis
COURSES
All Courses
Financial Analyst All in One Course
Investment Banking Course
Financial Modeling Course
Private Equity Course
Venture Capital Course
Excel All in One Course

Copyright © 2021. CFA Institute Does Not Endorse, Promote, Or Warrant The Accuracy Or Quality Of WallStreetMojo. CFA® And Chartered Financial Analyst® Are Registered Trademarks Owned By CFA Institute.
Return to top

WallStreetMojo

Free Excel Course

Excel functions, Formula, Charts, Formatting creating excel dashboard & others

* Please provide your correct email id. Login details for this Free course will be emailed to you

Book Your One Instructor : One Learner Free Class
Let’s Get Started
Please select the batch
Saturday - Sunday 9 am IST to 5 pm IST
Saturday - Sunday 9 am IST to 5 pm IST

This website or its third-party tools use cookies, which are necessary to its functioning and required to achieve the purposes illustrated in the cookie policy. By closing this banner, scrolling this page, clicking a link or continuing to browse otherwise, you agree to our Privacy Policy

WallStreetMojo

Free Excel Course

Excel functions, Formula, Charts, Formatting creating excel dashboard & others

* Please provide your correct email id. Login details for this Free course will be emailed to you

Login

Forgot Password?

WallStreetMojo

Download VBA Hide Columns Excel Template

New Year Offer - VBA Training Course (6 courses, 35+ hours video) View More