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.
2. Hide Columns Based on Criteria in Real-time with Excel VBA
Moreover, we can dynamically hide a column. 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:
- First, 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.
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.
Read More: Excel VBA to Hide Columns Using Column Number
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.
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.
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. Don’t forget to drop comments, suggestions, or queries if you have any in the comment section below.
