VBA Hide Columns

What is Excel VBA Hide Columns?

Excel VBA Hide Columns is a feature that allows you to hide a specific column/columns within a Microsoft Excel worksheet with VBA code. This feature is handy for automating tasks, organizing data, or customizing the visibility of columns based on certain conditions. By hiding columns, you can make your worksheets more user-friendly and display only the information that is relevant to your analysis or presentation.

Let us look at an example. Here, we have a subroutine named HideColumnByIndex with a straightforward purpose: to hide a specific column.

The target column is column B, which corresponds to an index of 2. The code utilizes the Columns property, which represents all columns in the worksheet, and within the parentheses, it precisely identifies column B by its index. The code accomplishes the task of hiding column B by setting the “.Hidden” property to True.

Upon executing this VBA macro, column B in the Excel worksheet is hidden from view.

Syntax

Here’s the basic syntax:

Columns(ColumnIndex).Hidden = True

Term	Description
Columns	This property represents all the columns in the worksheet.
ColumnIndex	Replace this with the index or number of the column you want to hide.
.Hidden	This property determines whether the column is hidden (set it to True to hide).

Excel VBA – All in One Courses Bundle (35+ Hours of Video Tutorials)

If you want to learn Excel and VBA professionally, then ​Excel VBA All in One Courses Bundle​ (35+ hours) is the perfect solution. Whether you’re a beginner or an experienced user, this bundle covers it all – from Basic Excel to Advanced Excel, Macros, Power Query, and VBA.

How to Hide Columns in VBA?

Hiding columns in VBA involves using the Columns property and the “.Hidden” property. Here’s a step-by-step guide on how to use VBA Hide columns.

Step 1: Open Excel and press ALT + F11 to open the VBA editor.

Step 2: Insert a new module by clicking on “Insert” in the menu and selecting “Module.”

Step 3: Write a VBA subroutine (a code block) to hide the desired columns.

Step 4: Customize your VBA code to specify which columns to hide based on your requirements. You can hide a single column or multiple columns or even apply conditions to hide columns dynamically.

Step 5: Run the VBA macro to execute the code and hide the specified columns. You can trigger the macro through the VBA editor or by assigning it to a button or event in your Excel worksheet.

Step 6: To unhide a hidden column, use the same syntax but set .Hidden to False. It will make the hidden column visible again.

Columns(ColumnIndex).Hidden = False

Examples

Example #1 – Hide using Range Object:

In this example, our goal is to hide a specific column, which is column C, in our Excel worksheet using the Range object ‘C:C’.

Step 1: In the new module, we begin by creating a VBA subroutine named HideColumnByRange.

Step 2: Inside the subroutine, we access all columns in the worksheet using the Columns property.

Step 3: To specify which column to hide, we use the Range object “C:C” inside double quotes. This Range object in VBA defines the exact range of cells within column C. By encapsulating it in double quotes, we instruct Excel that we want to work with the entire column C.

Step 4: Finally, we set the .Hidden property of the selected column to True. This action hides the entire column C from view.

Step 5: Now, save the macro and click on Run. When you execute this macro, you’ll see that column C in your Excel worksheet becomes hidden, making it no longer visible.

Here is the full code:

Sub HideColumnByRange()
‘ Hide column C
Columns(“C:C”).Hidden = True
End Sub

Example #2 – Hide using Columns Property

In this example, we aim to hide a specific column, which is column B (the second column), in our worksheet.

Step 1: We start by creating a VBA subroutine called HideColumnByProperty.

Step 2: Within the subroutine, we again use the Columns property to work with columns.

Step 3: Instead of specifying the column by name, we directly indicate the column’s index, which is 2 for column B.

Step 4: Just like in the previous example, we set the .Hidden property to True.

Step 5: When you run this macro, column B (the second column) in your worksheet becomes hidden.

Here is the full code:

Sub HideColumnByProperty()
‘ Hide column with index 2 (B)
Columns(2).Hidden = True
End Sub

Example #3 – Hide Multiple Columns:

In this case, our objective is to hide a range of columns, specifically columns D through G.

Step 1: We initiate a VBA subroutine named HideMultipleColumns.

Step 2: Inside this subroutine, we once again use the Columns property in VBA.

Step 3: To specify the range of columns we want to hide, we provide their references in double quotes, “D:G.”

Step 4: By setting the .Hidden property to True, we effectively hide all columns within the range D to G.

Step 5: When you execute this macro, you’ll observe that columns D, E, F, and G are hidden from view.

Here is the full code:

Sub HideMultipleColumns()
‘ Hide columns D to G
Columns(“D:G”).Hidden = True
End Sub

Example #4 – Hide Columns with Single Cell:

In this example, our goal is to hide a column (in this case, column E) based on the content of a single cell, E1.

Step 1: We create a VBA subroutine named HideColumnWithSingleCell.

Step 2: Inside the subroutine, we use the IsEmpty function to check whether cell E1 is empty.

Step 3: If cell E1 is indeed empty, we proceed into the conditional block.

Step 4: Within the conditional block, we utilize the Columns property to hide column E.

Step 5: When you run this macro, it examines whether E1 contains data; if it’s empty, column E is hidden. Otherwise, it remains visible.

Here is the full code:

Sub HideColumnWithSingleCell()
‘ Hide column E if cell E1 is empty
If IsEmpty(Range(“E1”)) Then
Columns(“E:E”).Hidden = True
End If
End Sub

Example #5 – Hide Every Alternative Column:

This example demonstrates how to hide every other column, starting from column B. Below, we have some data in a worksheet.

Step 1: We create a VBA subroutine named HideAlternativeColumns.

Step 2: Inside the subroutine, we declare “col” as an integer, and we introduce a loop variable col to iterate through column indices.

Step 3: The loop begins at index 2 (which corresponds to column B) and increments by 2 in each iteration. It effectively targets every second column.

Step 4: Within each iteration of the loop, we utilize the Columns property to hide the column specified by the col variable.

Step 5: When you execute this macro, columns B, D, F, and so on, will be hidden, leaving every alternate column hidden.

Here is the full code:

Sub HideAlternativeColumns()
Dim col As Integer
‘ Loop through columns and hide every other column starting from column B
For col = 2 To Columns.Count Step 2
Columns(col).Hidden = True
Next col
End Sub

Example #6 – Hide Every Empty Column

This example showcases how to hide columns that contain only empty cells. In this example, column K and every column after N is empty.

Step 1: We create a VBA subroutine called HideEmptyColumns.

Step 2: Inside the subroutine, we use a loop variable col to iterate through the column indices.

Step 3: The loop begins at index 1 and goes through all columns. Here, Columns.Count finds the count of the columns.

Step 4: Within each iteration of the loop, we employ the WorksheetFunction.CountA function to count non-empty cells in the current column (Columns(col)).

Step 5: If the count is 0 (meaning all cells are empty), we use the Columns property to hide that column.

Step 6: Running this macro hides any columns that do not contain data. In this example, you can see that column K and every column after N are hidden as they do not contain any data.

Here is the full code:

Sub HideEmptyColumns()
Dim col As Integer
‘ Loop through columns and hide if all cells in the column are empty
For col = 1 To Columns.Count
If WorksheetFunction.CountA(Columns(col)) = 0 Then
Columns(col).Hidden = True
End If
Next col
End Sub

Example #7 – Hide Columns Based On Cell Value

In this example, our objective is to hide columns based on specific cell values in the first row.

Step 1: We create a VBA subroutine named HideColumnsBasedOnCellValue. And declare the cell as the range.

Step 2: Inside this subroutine, we employ a For Each loop to iterate through cells in the first row.

Step 3: For each cell encountered in the loop, we check its value.

Step 4: If the value of the cell equals “Hide,” we enter the conditional block.

Step 5: Inside the conditional block, we use EntireColumn to access the entire column corresponding to the cell and set its .Hidden property to True.

Step 6: When you run this macro, it hides columns based on whether the corresponding cell in the first row contains the value “Hide.”

Here is the full code:

Sub HideColumnsBasedOnCellValue()
Dim cell As Range
‘ Loop through cells in row 1 and hide columns based on cell value
For Each cell In Range(“1:1”)
If cell.Value = “Hide” Then
cell.EntireColumn.Hidden = True
End If
Next cell
End Sub

Important Things To Note

1. To unhide a hidden column, you can set the .Hidden property to False.
2. You can apply VBA Hide Columns Using Column Number to hide columns directly by specifying their column numbers, providing a simple and precise way to control data visibility within your Excel worksheet.
3. You can use the VBA Hide Columns Based on Cell Value method to hide columns in Excel based on specific cell values, providing dynamic control over data visibility within your worksheet.

Frequently Asked Questions (FAQs)

Download Template

This article must be helpful to understand the VBA Hide Columns, with its features and examples. You can download the template here to use it instantly.

Recommended Articles

This has been a guide to VBA Hide Columns in Excel. Here we explain how to use hide column property to hide different column in excel, based on cell value along with syntax, examples & downloadable excel template. You can learn more from the following articles –

• VBA Insert Columns
• VBA UsedRange
• VBA Join