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…
50+ Project Management Templates Pack
Excel PowerPoint Word
Advanced Project Plan & Portfolio Template
Business Presentations Templates Pack
20+ Excel Project Management Pack
20+ PowerPoint Project Management Pack
10+ MS Word Project Management Pack
- Hide UnHide Columns in Excel Worksheet using VBA – An Example
- Hide UnHide Columns in Excel Worksheet using VBA – Case study
- Hide UnHide Columns in Excel Worksheet using VBA – Download Example Files
- Hide Columns Based On Condition in Excel Worksheet using VBA
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.
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
- Open an excel workbook
- Enter some data in the Range A1 to F5
- Press Alt+F11 to open VBA Editor
- Insert a Module for Insert Menu
- Copy the above code and Paste in the code window
- Save the file as macro enabled workbook
- Press F5 to run it
Download the example file available at end of the topic and execute the macros.
Here is sample screen-shot of the example file.
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 in Excel Worksheet using VBA – Download Example File
You can download the example file and see the code for each button.
ANALYSISTABS – 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 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 macro to check if row 1 of a column is 0 or blank and hide:
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
Related ResourceExternal VBA Reference
Excel VBA Reference Project Management Reference VBA Reference:
- VBA Code Explorer
- VBA Excel Application
- VBA Excel Workbook
- VBA Excel Worksheet
- VBA Excel Range
- VBA ActiveX Controls
- VBA Userforms
- VBA Projects
- What is a Project?
- Project Appraisal
- Project Management
- Project Plan
- Project Resource
- What is Gantt Chart?
- Excel Templates
- Excel Project Management Templates
- PowerPoint Project Management Templates
- MS Word Project Management Templates