Excel VBA to Hide Columns Based on Criteria (6 Useful Examples)

Excel is a powerful software. We can perform numerous operations on our datasets using Excel tools and features. Moreover, VBA is another cool application of Microsoft Excel. Those who love coding will love this. With some simple codes, you can carry out difficult tasks within a very short time. Sometimes, we need to hide some columns in our Excel worksheets for various reasons. In this article, we’ll show you 6 useful examples of Excel VBA to Hide Columns Based on Criteria.

Excel VBA to Hide Columns Based on Criteria: 6 Useful Examples

To illustrate, we’ll use a sample dataset as an example. For instance, the following dataset represents the Salesman, Product, and Net Sales. In this dataset, we’ll hide a column or some columns based on specific criteria.

1. Apply Excel VBA to Hide Columns Based on Cell Value

In our first example, you’ll see how to hide a column based on a cell value. Here, we have X in a cell. We’ll hide the entire column that has this cell value. Therefore, follow the steps below to perform the task.

STEPS:

• First, go to the Developer tab.
• Then, select Visual Basic.

• As a result, the VBA window will pop out.
• Now, select Insert ➤ Module.

• Consequently, the Module window will appear.
• Next, copy the following code and paste it into the box.

Sub HideCol_CellValue()
Dim c As Range
For Each c In ActiveWorkbook.ActiveSheet.Rows("10").Cells
If c.Value = "X" Then
c.EntireColumn.Hidden = True
End If
Next c
End Sub

• Save the file and press F5 to run the code.
• After that, close the VBA window.
• Finally, you’ll see the required changes.
• See the below picture to have a better understanding.

Read More: Excel VBA: Hide Columns Based on Cell Value 

2. Hide Columns Based on Criteria in Real-time with Excel VBA

Moreover, we can hide a column in a dynamic way. In this example, we’ll input the column header which we’ll keep in the worksheet. Now, learn the following steps to carry out the operation.

STEPS:

• Firstly, double-click on the sheet name.
• Subsequently, click View Code.

• Hence, a dialog box will emerge in the VBA window.
• Copy the below code and paste it there.

Private Sub Worksheet_SelectionChange(ByVal Target As Range)
If Range("G4").Value = "Product" Then
Columns("D").EntireColumn.Hidden = True
ElseIf Range("G4").Value = "Net Sales" Then
Columns("C").EntireColumn.Hidden = True
End If
End Sub

• Afterward, in cell G4, type Product.
• And immediately, the Net Sales column will get hidden.

Read More: How to Hide and Unhide Columns in Excel

3. Embed VBA for Hiding a Single Column in Excel

However, we may want to hide a single column. We can input the column number or name in the VBA code to hide the column. So, follow the process to hide a single column.

STEPS:

• First of all, select Developer ➤ Visual Basic.
• Then, click Insert ➤ Module.
• There, paste the following code.

Sub Hide_Column()
    Columns("C").Hidden = True
End Sub

• Press F5 to run the code.
• Thus, it’ll hide column C.

4. Hide Multiple Columns in Excel with VBA

We can also hide multiple columns at once. We’ll construct the VBA code in that way. Hence, learn the process for performing the task.

STEPS:

• In the Developer tab, choose Visual Basic at first.
• Open the Module window by clicking Module from the Insert drop-down.
• Copy the code and place it there.

Sub Hide_MultipleColumns()
    Columns("C:D").EntireColumn.Hidden = True
End Sub

• Run the code by pressing F5.
• In this way, you can hide multiple columns.
• In this example, we hide the C and D columns.

Read More: How to Hide Selected Columns in Excel 

5. Use VBA to Hide Every Alternative Column

Similarly, we can hide the alternative columns. Here, we’ll hide C and E. Now, go through the process to hide every Alternative Column.

STEPS:

• In the beginning, choose Visual Basic from the Developer tab.
• Click Insert ➤ Module.
• Insert the following code there.

Sub Hide_AlternativeColumns()
    Dim p As Integer
    For p = 2 To 4
        Cells(1, p + 1).EntireColumn.Hidden = True
        p = p + 1
    Next p
End Sub

• Save the file.
• Press F5 to run the code.
• As a result, it’ll hide the C and E columns.

Read More: How to Hide Columns with Button in Excel

6. Hide Empty Columns with VBA in Excel

It’s necessary to have a VBA code that will hide the empty columns. In this example, column C is empty. Therefore, see the steps to carry out the operation.

STEPS:

• Go to Developer ➤ Visual Basic.
• In the Module window, paste the below code.

Sub Hide_EmptyColumns()
    Dim p As Integer
    For p = 2 To 4
       If Cells(4, p).Value = "" Then
             Columns(p).Hidden = True
       End If
    Next p
End Sub

• Save the file.
• Run the code by pressing F5.
• Hence, you’ll get the desired outcome.

Download Practice Workbook

Download the following workbook to practice by yourself.

Conclusion

Henceforth, you will be able to apply Excel VBA to Hide Columns Based on Criteria following the above-described examples. Keep using them and let us know if you have more ways to do the task. Follow the ExcelDemy website for more articles like this. Don’t forget to drop comments, suggestions, or queries if you have any in the comment section below.

Related Articles

• How to Unhide Columns in Excel All at Once
• Excel VBA to Hide Columns Using Column Number
• Unhide Columns in Excel Shortcut Not Working 
• How to Hide Columns in Excel with Minus or Plus Sign
• How to Unhide Columns in Excel
• How to Unhide All Columns with Excel VBA
• Unhide Columns Is Not Working in Excel
• How to Collapse Columns in Excel
• How to Group and Hide Columns in Excel
• How to Hide Columns in Excel with Password
• How to Hide Extra Columns in Excel
• How to Hide Columns Without Right Click in Excel