Hide UnHide Columns in Excel Worksheet using VBA

Home/Excel VBA/Hide UnHide Columns in Excel Worksheet using VBA

Description:

We may need to Hide UnHide the Columns in Excel for some types of requirements. For examples we may have data for 3 different categories and want to show it based on the user selection. We can achieve this by Hiding or Un-hiding the Columns in Excel. We will see this in the following examples…

PREMIUM TEMPLATES LIMITED TIME OFFER

ON SALE80% OFF

50+ Project Management Templates Pack
Excel PowerPoint Word

Advanced Project Plan & Portfolio Template
Excel Template

Business Presentations Templates Pack
PowerPoint Slides

20+ Excel Project Management Pack
Excel Templates

20+ PowerPoint Project Management Pack
PowerPoint Templates

10+ MS Word Project Management Pack
Word Templates


Hide UnHide Columns in Excel Worksheet using VBA – Solution

We can use EntireColumn.Hidden property of a Column. You can set the property value to TRUE if you want to hide, Set to FALSE if you want to un-hide the Columns.

Hide-UnHide Columns in Excel Worksheet using VBA – An Example

The following example will show you how to hide and unhide the Columns in Excel using VBA. In this example I am hiding the Columns B,C and D by setting the Hidden property as TRUE. And then un hiding by setting the Hidden=FALSE.

Code:
Sub sbHidingUnHideColumns()
'To Hide Columns B to D
Columns("B:D").EntireColumn.Hidden = True
'To UnHide Columns B to D
Columns("B:D").EntireColumn.Hidden = False
End Sub
Instructions:
  1. Open an excel workbook
  2. Enter some data in the Range A1 to F5
  3. Press Alt+F11 to open VBA Editor
  4. Insert a Module for Insert Menu
  5. Copy the above code and Paste in the code window
  6. Save the file as macro enabled workbook
  7. Press F5 to run it
Input:

Download the example file available at end of the topic and execute the macros.

Output:

Here is sample screen-shot of the example file.
Hide Unhide Columns Examples

Hide-UnHide Columnsin Excel Worksheet using VBA – Case study

Here is the example case study. You can download the file below and see how its working. You can click on the buttons to view the respective data.

Hide Unhide Columns Examples Case Study

Hide Unhide Columns Examples Case Study

Hide Unhide Columns Examples Case Study 3

Hide-UnHide Columns in Excel Worksheet using VBA – Download Example File

You can download the example file and see the code for each button.
mongopono.ru – Hide-Unhide Columns

Hide Columns Based On Condition in Excel Worksheet using VBA

We can hide the columns based on certain condition. The below macro will loop through the first 25 columns and check if the value in the row 1 is 0, then hide it.

Example 1

Example macro to check if row 1 of a column is 0 and hide:

Sub hideColumnsBasedOnConditionZero()
LastColumn = 25 'Last Column
For i = 1 To LastColumn 'Lopping through each Column
'Hide all the columns with the values as 0 in Row 1
If Cells(1, i) = 0 And Cells(1, i) <> "" Then Columns(i).EntireColumn.Hidden = True
Next
End Sub

Example 2

Example macro to check if row 1 of a column is 0 or blank and hide:


Sub hideColumnsBasedOnConditionZeroBlank()
LastColumn = 25 ‘Last Column
For i = 1 To LastColumn ‘Lopping through each Column
‘Hide all the columns with the values as 0 in Row 1
If Cells(1, i) = 0 or Cells(1, i) = “” Then Columns(i).EntireColumn.Hidden = True

Next
End Sub

LIMITED TIME OFFER
By |March 16th, 2013|Excel VBA|3 Comments

About the Author:

Excel VBA Developer having around 8 years of experience in using Excel and VBA for automating the daily tasks, reports generation and dashboards preparation. Valli is sharing to helps us automating daily tasks.

3 Comments

  1. Michael Ramírez January 7, 2016 at 1:02 AM - Reply

    Hello.

    Are you able to add a data list on A1, and depending on what you choose, it activates an specific Macro?

    For example. I choose (in A1) from a drop down list “Room 2” it will hide everything but room 2?

    I hope to hear from you soon, thank you very much for your example, I did learn more from it.

    Pura Vida from Costa Rica

  2. phani raj November 26, 2016 at 12:44 PM - Reply

    Sir,

    I am new to this subject. I want to hide rows from 10 to 22 if value at D7 is not 3. Please guide me for the same. Regards, phani

  3. Nayon December 12, 2016 at 8:40 PM - Reply

    I want Hide & unhidden column by using macro in excel. suppose by data validation i select “A” then Column “B:E” will show with specific column other column will be hidden.

    Is it possible ?????

    Note: I am new user of Macro.

Leave A Comment


Related pages


vlookup function examplesproject management excel gantt chart templatelcase vbaunprotect excel sheet 2013how to remove duplicate records in excelsort the selected cells by last name in ascending ordercreate sql statement from excelexcel form creation tutorialsort excel vbahow to make a pivot table in excel 2007vba sheet cellssql commands with syntax and examples pdfhow to connect excel to sql server databaseremove blank lines in exceldynamic excel dashboardexcel vba delete rowsvba listboxexcel sql queryexcel activeworkbook.saveasdefine worksheet in excelvba object modelaccess macro tutorialinsert tick box in excelexcel 2007 checkboxvba manualexcel vba deletehow to display developer tab in excel 2010sas code tutorialsub vbamultiple checkboxes in excelvlookup definitionexcel coding tutorialworkbook open vbavba pop up windowwhat is pivot chart in excelhow to send email using vbabest excel vba book for beginnersexcel workbook vbavba symbolexcel lookup syntaxexcel remove all blank rowsvba casesinsert checkbox in excel 2007unlock excel cells without passwordlistbox examplecombine worksheetspaste special excel 2007free excel dashboard templateexcel vba block commenthow to remove password protection in excel 2010nested if else in excelwrite macro excelwhat is the match function in excelreturn vbavba excel delete columnadodb providerexcel tutorial vbahyperlink addressfor loop in macro excelduplicate cell excelsimple vba codevba wait commandrun query in excelmacros excel 2007excel vba codeaccess vba examplesvba range variablewhat is the use of vlookup function in excelvba promptmacro programming excelexcel macro file locationvba excel select rowexcel shortcuts keys listiserror vbamultiple criteria in excelvba do loops