Learn How to Hide Columns Dynamically with VBA Code in Excel

VBA stands for Visual Basic for Applications, which is a programming language that allows you to automate tasks in Excel and other Microsoft Office applications. One of the tasks that you can perform with VBA is to hide or unhide columns based on certain criteria or conditions.

Hiding columns can be useful when you want to focus on a specific subset of data, or when you want to protect some sensitive information from being seen by others. You can hide columns manually by right-clicking on the column header and selecting Hide, or by using the Format menu on the Home tab. However, this can be tedious and time-consuming if you have to hide multiple columns or if you want to hide columns dynamically based on some values or formulas.

Using VBA, you can write a code that can hide columns in specific intervals, such as every other column, every third column, every nth column, etc. You can also hide columns based on the values in a certain cell or range, such as hiding columns that contain zeros, blanks, negative numbers, etc. You can also use logical operators and functions to create more complex conditions for hiding columns, such as hiding columns that match a certain text, date, or color.

Procedures

To write a VBA code to hide columns in specific intervals, you need to follow these steps:

1. Open the workbook that contains the data that you want to hide columns from.
2. Press Alt + F11 to open the Visual Basic Editor (VBE).
3. In the Project Explorer window, find the name of the worksheet that contains the data, and double-click on it to open the code window.
4. In the code window, type or paste the VBA code that can hide columns in specific intervals. You can use the examples below as a reference, or modify them according to your needs.
5. Save the workbook and close the VBE.
6. To run the code, you can either press F5 in the VBE, or assign a macro to a button, a keyboard shortcut, or a worksheet event.

Examples

Example 1: Hide every other column

The following code will hide every other column in the active worksheet, starting from column A.

Sub HideEveryOtherColumn()
    Dim i As Long
    For i = 1 To Columns.Count Step 2 'Loop through every other column
        Columns(i).Hidden = True 'Hide the column
    Next i
End Sub

Example 2: Hide every nth column

The following code will hide every nth column in the active worksheet, where n is a variable that you can change. In this example, n is set to 3, so the code will hide every third column, starting from column A.

Sub HideEveryNthColumn()
    Dim i As Long
    Dim n As Long
    n = 3 'Change this to the desired interval
    For i = 1 To Columns.Count Step n 'Loop through every nth column
        Columns(i).Hidden = True 'Hide the column
    Next i
End Sub

Example 3: Hide columns based on a cell value

The following code will hide columns based on the value in cell A1. If the value in cell A1 is “X”, the code will hide columns B and C. If the value in cell A1 is “Y”, the code will hide columns D and E. Otherwise, the code will do nothing.

Sub HideColumnsBasedOnCellValue()
    If Range("A1").Value = "X" Then 'Check the value in cell A1
        Columns("B:C").Hidden = True 'Hide columns B and C
    ElseIf Range("A1").Value = "Y" Then 'Check the value in cell A1
        Columns("D:E").Hidden = True 'Hide columns D and E
    Else
        'Do nothing
    End If
End Sub

Explanation

To understand how the VBA code works, let’s go through each line of the code and explain what it does.

• Sub HideEveryOtherColumn(): This line defines the name of the subroutine, which is a block of code that performs a specific task. You can name the subroutine anything you want, as long as it follows the naming rules for VBA identifiers. The parentheses after the name indicate that the subroutine does not take any arguments or parameters.
• Dim i As Long: This line declares a variable named i, and assigns it the data type Long, which is a numeric data type that can store large integers. A variable is a name that represents a value that can change during the execution of the code. Declaring a variable means specifying its name and data type, so that VBA can allocate memory space for it and know how to handle it. You can declare variables using the Dim keyword, followed by the variable name and the data type. You can also use other keywords, such as Private, Public, or Static, to specify the scope and lifetime of the variable.
• For i = 1 To Columns.Count Step 2: This line starts a For loop, which is a control structure that allows you to repeat a block of code a certain number of times, or until a condition is met. A For loop has the following syntax:

For counter = start To end [Step increment]
    'Code to be executed
Next [counter]

The counter is a variable that keeps track of the number of iterations of the loop. The start and end are numeric expressions that specify the initial and final values of the counter. The increment is an optional numeric expression that specifies how much the counter changes after each iteration. If the increment is omitted, it is assumed to be 1. The Next statement marks the end of the loop, and optionally repeats the name of the counter.

In this example, the counter is i, the start is 1, the end is Columns.Count, and the increment is 2. Columns.Count is a property that returns the number of columns in the worksheet. Therefore, the loop will iterate from 1 to the number of columns, with a step of 2. This means that the loop will execute for every other column, starting from column A.

• Columns(i).Hidden = True: This line is the code that is executed inside the loop. Columns is a collection object that represents all the columns in the worksheet. You can access a specific column by using its index number or letter, such as Columns(1) or Columns(“A”). Hidden is a property that returns or sets a Boolean value that indicates whether the column is hidden or not. True means hidden, and False means visible. Therefore, this line sets the Hidden property of the column with the index i to True, which means hiding the column.
• Next i: This line marks the end of the loop, and repeats the name of the counter i. This line also increments the counter by the specified step, and checks if the counter has reached the end value. If not, the loop repeats from the beginning. If yes, the loop exits and the code continues after the Next statement.

The rest of the examples follow the same logic, with some variations in the loop conditions and the Hidden property assignments. You can modify the code to suit your needs, such as changing the interval, the cell reference, the column range, or the criteria for hiding columns.