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

Get FREE Advanced Excel Exercises with Solutions!

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.

Apply Excel VBA to Hide Columns Based on Cell Value

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

Apply Excel VBA to Hide Columns Based on Cell Value

  • 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

Apply Excel VBA to Hide Columns Based on Cell Value

  • 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.

Apply Excel VBA to Hide Columns Based on Cell Value

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.

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

  • 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

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

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

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

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.

Hide Empty Columns with VBA in Excel

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

Hide Empty Columns with VBA in Excel

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

Hide Empty Columns with VBA in Excel


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

Aung Shine
Aung Shine

My name is Aung. I have my B.Sc. degree in EEE. From now on, I will be working with Microsoft Excel and other useful software, and I’ll upload articles related to them. My current goal is to write technical contents for anybody and everybody that will make the learning process of new software and features a happy journey.

We will be happy to hear your thoughts

Leave a reply

Advanced Excel Exercises with Solutions PDF

 

 

ExcelDemy
Logo