Excel VBA to Hide Columns Based on Cell Value (15 Examples)

Sometimes, we need to hide columns based on some specific cell value. It is a pretty common task. In this article, we are going to demonstrate 15 examples to hide columns based on a cell value using Excel VBA. If you are curious about it, download our practice workbook and follow us.

Excel VBA to Hide Columns Based on Cell Value: 15 Suitable Examples

To demonstrate our examples, we consider a dataset of 5 students. Their name, department, examination marks, grades, DOB, and retake examination marks are in the range of cells B5:H9. In our examples, we will hide columns based on different cell value criteria.

1. Hide Columns Based on Cell Text Value with VBA

In this example, we are going to hide columns based on a specific text value of the cell. We will hide the columns for cell value Chemistry.

The steps to complete this example are given as follows:

📌 Steps:

• First of all, go to the Developer tab and click on Visual Basic. If you don’t have that, you have to enable the Developer tab. You can also press ‘Alt+F11’ to open the Visual Basic Editor.

• A dialog box will appear.
• Now, in the Insert tab on that box, click Module.

• Then, write down the following visual code in that empty editor box.

Sub Hide_Columns_on_Cell_Text_Value()
    StartColumn = 2
    LastColumn = 10
    iRow = 6
    For i = StartColumn To LastColumn
        If Cells(iRow, i).Value <> "Chemistry" Then
        Cells(iRow, i).EntireColumn.Hidden = False
        Else
        Cells(iRow, i).EntireColumn.Hidden = True
        End If
    Next i
End Sub

• Press ‘Ctrl+S’ to save the code.
• Close the Editor tab.
• After that, in the Developer tab, click on Macros, located in group Code.

• As a result, a new dialog box called Macro will appear. Select Hide_Columns_on_ Cell_Text_Value.
• Finally, click on the Run button to run this code.

• You will see the column which contains the text Chemistry, disappears from our dataset.

Thus, we can say that our visual code worked perfectly, and we are able to apply Excel VBA to hide columns based on cell value.

Read More: Excel VBA to Hide Columns Based on Criteria

2. Hide Column Based on Cell Numeric Value

In the following example, we will hide columns based on a specific numeric value. In this case, the value is 87.

The steps to complete this example are given below:

📌 Steps:

• First, go to the Developer tab and click on Visual Basic. If you don’t have that, you have to enable the Developer tab. You can also press ‘Alt+F11’ to open the Visual Basic Editor.

• A dialog box will appear.
• After that, in the Insert tab on that box, click Module.

• Now, write down the following visual code in that empty editor box.

Sub Hide_Columns_on_Cell_Numeric_Value()
    StartColumn = 2
    LastColumn = 8
    iRow = 6
    For i = StartColumn To LastColumn
        If Cells(iRow, i).Value <> "87" Then
        Cells(iRow, i).EntireColumn.Hidden = False
        Else
        Cells(iRow, i).EntireColumn.Hidden = True
        End If
    Next i
End Sub

• Press ‘Ctrl+S’ to save the code.
• Close the Editor tab.
• Then, in the Developer tab, click on Macros, located in group Code.

• As a result, a new dialog box called Macro will appear.
• Select Hide_Columns_on _Cell_Numeric_Value and click on the Run button to run this code.

• You will see the column which contains the number 87, hidden from our dataset.

So, we can say that our visual code worked effectively, and we are able to apply Excel VBA to hide columns based on cell value.

Read More: Excel VBA to Hide Columns Using Column Number 

3. Hide Columns Where Cells Contain Texts

Now, we will show you the process to hide all columns where all cells contain text values.

The steps of this example are shown below:

📌 Steps:

• First, go to the Developer tab and click on Visual Basic. If you don’t have that, you have to enable the Developer tab. You can also press ‘Alt+F11’ to open the Visual Basic Editor.

• As a result, a dialog box will appear.
• Afterward, in the Insert tab on that box, click Module.

• Then, write down the following visual code in that empty editor box.

Sub Hide_Columns_Contain_Text()
    StartRow = 5
    LastRow = 9
    StartCol = 2
    LastCol = 8
    For i = StartRow To LastRow
        For j = StartCol To LastCol
        If IsNumeric(Cells(i, j)) = False Then
        Columns(j).EntireColumn.Hidden = True
        End If
        Next j
    Next i
End Sub

• Press ‘Ctrl+S’ to save the code.
• At last, close the Editor tab.
• Now, in the Developer tab, click on Macros, located in group Code.

• As a result, a new dialog box called Macro will appear. Select Hide_Columns_ Contains_Text.
• Next, click on the Run button to run this code.

• You will see all the columns which contain text, disappear from our dataset.

Hence, we can say that our visual code worked precisely, and we are able to apply Excel VBA to hide columns based on cell value.

Read More: How to Hide Columns with Button in Excel

4. Hide Columns Where Cells Contain Number

Here, we will hide all columns where all cells contain numeric or number values.

The steps of this example are shown as follows:

📌 Steps:

• First, go to the Developer tab and click on Visual Basic. If you don’t have that, you have to enable the Developer tab. You can also press ‘Alt+F11’ to open the Visual Basic Editor.

• As a result, a dialog box will appear.
• Then, in the Insert tab on that box, click Module.

• Next, write down the following visual code in that empty editor box.

Sub Hide_Columns_Contain_Number()
    StartRow = 5
    LastRow = 9
    StartCol = 2
    LastCol = 8
    For i = StartRow To LastRow
        For j = StartCol To LastCol
        If IsNumeric(Cells(i, j)) = True Then
        Columns(j).EntireColumn.Hidden = True
        End If
        Next j
    Next i
End Sub

• Press ‘Ctrl+S’ to save the code.
• Close the Editor tab.
• Now, in the Developer tab, click on Macros, located in group Code.

• As a result, a new dialog box called Macro will appear.
• Select Hide_All_Columns_Contain_Number and click on the Run button to run this code.

• You will see all the columns which contain the numeric values disappear from our dataset.

In the end, we can say that our visual code worked successfully, and we are able to apply Excel VBA to hide columns based on cell value.

Read More: How to Hide Multiple Columns in Excel 

5. Hide Columns for Zero (0) Cell Value

In this example, we will hide that column where at least a cell contains Zero (0) values. We have a Zero (0) value in our dataset in cell E7.

The steps of this example are shown as follows:

📌 Steps:

• First, go to the Developer tab and click on Visual Basic. If you don’t have that, you have to enable the Developer tab. You can also press ‘Alt+F11’ to open the Visual Basic Editor.

• A dialog box will appear.
• Now, in the Insert tab on that box, click Module.

• Next, write down the following visual code in that empty editor box.

Sub Hide_Columns_for_Zero()
    StartColumn = 2
    LastColumn = 8
    iRow = 7
    For i = StartColumn To LastColumn
        If Cells(iRow, i).Value <> "0" Then
        Cells(iRow, i).EntireColumn.Hidden = False
        Else
        Cells(iRow, i).EntireColumn.Hidden = True
        End If
    Next i
End Sub

• Press ‘Ctrl+S’ to save the code.
• Next, close the Editor tab.
• After that, in the Developer tab, click on Macros, located in group Code.

• As a result, a new dialog box called Macro will appear. Select Hide_Columns_for _Zore.
• Then, click on the Run button to run this code.

• You will see the column which contains the Zero (0) values, hidden from our dataset.

At last, we can say that our visual code worked perfectly, and we are able to apply Excel VBA to hide columns based on cell value.

Read More: How to Hide Selected Columns in Excel 

6. Defining Row Number to Hide Columns

In the following example, we are going to define the row number and the character to hide that column. We will search for our desired character B in row 7.

The steps are explained below as follows:

📌 Steps:

• First, go to the Developer tab and click on Visual Basic. If you don’t have that, you have to enable the Developer tab. You can also press ‘Alt+F11’ to open the Visual Basic Editor.

• A dialog box will appear.
• After that, in the Insert tab on that box, click Module.

• Now, write down the following visual code in that empty editor box.

Sub Hide_Columns_Through_Row_Number()
Dim A As Range
    For Each A In ActiveWorkbook.ActiveSheet.Rows("7").Cells
        If A.Value = "B" Then
        A.EntireColumn.Hidden = True
        End If
    Next A
End Sub

• Press ‘Ctrl+S’ to save the code.
• Then, close the Editor tab.
• Afterward, in the Developer tab, click on Macros, located in group Code.

• As a result, a new dialog box called Macro will appear. Select Hide_Columns_ Through_Row_Number.
• Click on the Run button to run this code.

• You will see the column which contains our chosen B character, disappears from our dataset.

Finally, we can say that our visual code worked precisely, and we are able to apply Excel VBA to hide columns based on cell value.

7. Hide Columns Based on Cell’s Negative Value

We can set our criteria to a negative value to hide a column. For this example, we consider that we have a negative value at cell E9, which is -12.

Now, we will show the steps to finish this task:

📌 Steps:

• First, go to the Developer tab and click on Visual Basic. If you don’t have that, you have to enable the Developer tab. You can also press ‘Alt+F11’ to open the Visual Basic Editor.

• As a result, a dialog box will appear.
• In the Insert tab on that box, click Module.

• Now, write down the following visual code in that empty editor box.

Sub Hide_Columns_Contain_Negative()
   StartColumn = 2
    LastColumn = 8
    iRow = 9
    For i = StartColumn To LastColumn
        If Cells(iRow, i).Value < "0" Then
        Cells(iRow, i).EntireColumn.Hidden = True
        Else
        Cells(iRow, i).EntireColumn.Hidden = False
        End If
    Next i
End Sub

• Press ‘Ctrl+S’ to save the code.
• Afterward, close the Editor tab.
• Then, in the Developer tab, click on Macros, located in group Code.

• A new dialog box called Macro will appear. Select Hide_Columns_Contain _Negative.
• Now, click on the Run button to run this code.

• You will see the column which contains the negative value, disappears from our dataset.

Thus, we can say that our visual code worked effectively, and we are able to apply Excel VBA to hide columns based on cell value.

Read More: How to Hide Columns in Excel with Minus or Plus Sign 

8. Hide Columns Based on Cell’s Positive Value

In addition, we can also set our criteria for positive values to hide a column. For that, we need a different dataset because you know that examination cannot provide in negative. So, we are considering a temperature chart of the first three months of some states of us. In the chart, the month of January and February contains the only negative value, and March has both negative and positive value.

After running the code, we will get column E hidden. The steps of this process are given below:

📌 Steps:

• First, go to the Developer tab and click on Visual Basic. If you don’t have that, you have to enable the Developer tab. You can also press ‘Alt+F11’ to open the Visual Basic Editor.

• As a result, a dialog box will appear.
• After that, in the Insert tab on that box, click Module.

• Write down the following visual code in that empty editor box.

Sub Hide_Columns_Contain_Positive()
    StartRow = 5
    LastRow = 9
    StartCol = 2
    LastCol = 8
    For i = StartRow To LastRow
        For j = StartCol To LastCol
        If IsNumeric(Cells(i, j)) = True Then
            If Cells(i, j).Value > 0 Then
            Columns(j).EntireColumn.Hidden = True
            End If
        End If
        Next j
    Next i
End Sub

• Press ‘Ctrl+S’ to save the code.
• Then, close the Editor tab.
• Next, in the Developer tab, click on Macros, located in group Code.

• A new dialog box called Macro will appear. Select Hide_Columns_Contain _Positive.
• Afterward, click on the Run button to run this code.

• You will see the column which contains the negative value, disappears from our dataset.

So, we can say that our visual code worked successfully, and we are able to apply Excel VBA to hide columns based on cell value.

9. Applying Macro to Hide Columns Where Cells Contain Odd Numbers

In the following example, we are going to hide those columns which contain the odd numbers. You may notice that in column E, there are three odd numbers.

The steps to complete this example are explained below as follows:

📌 Steps:

• First, go to the Developer tab and click on Visual Basic. If you don’t have that, you have to enable the Developer tab. You can also press ‘Alt+F11’ to open the Visual Basic Editor.

• As a result, a dialog box will appear.
• Now, in the Insert tab on that box, click Module.

• Then, write down the following visual code in that empty editor box.

Sub Hide_Columns_Contain_Odd()
    StartRow = 5
    LastRow = 9
    StartCol = 2
    LastCol = 8
    For i = StartRow To LastRow
        For j = StartCol To LastCol
        If IsNumeric(Cells(i, j)) = True Then
            If Cells(i, j).Value Mod 2 = 1 Then
            Columns(j).EntireColumn.Hidden = True
            End If
        End If
        Next j
    Next i
End Sub

• Press ‘Ctrl+S’ to save the code.
• Aftward, close the Editor tab.
• Now, in the Developer tab, click on Macros, located in group Code.

• A new dialog box called Macro will appear. Select Hide_Columns_Contain_Odd.
• Then, click on the Run button to run this code.

• You will see column E disappears from our dataset.

Hence, we can say that our visual code worked perfectly, and we are able to apply Excel VBA to hide columns based on cell value.

Read More: Excel Hide Columns Based on Cell Value without Macro

10. Apply Macro to Hide Columns Where Cells Contain Even Numbers

Like the odd number, we can hide columns for even numbers also. Now, we are going to show it. You may notice that in column H, there is one even number in cell H7.

The steps to complete this example are given below:

📌 Steps:

• First, go to the Developer tab and click on Visual Basic. If you don’t have that, you have to enable the Developer tab. You can also press ‘Alt+F11’ to open the Visual Basic Editor.

• As a result, a dialog box will appear.
• Now, in the Insert tab on that box, click Module.

• Then, write down the following visual code in that empty editor box.

Sub Hide_Columns_Contain_Even()
    StartRow = 5
    LastRow = 9
    StartCol = 2
    LastCol = 8
    For i = StartRow To LastRow
        For j = StartCol To LastCol
        If IsNumeric(Cells(i, j)) = True Then
            If Cells(i, j).Value Mod 2 = 0 Then
            Columns(j).EntireColumn.Hidden = True
            End If
        End If
        Next j
    Next i
End Sub

• Press ‘Ctrl+S’ to save the code.
• Aftward, close the Editor tab.
• Now, in the Developer tab, click on Macros, located in group Code.

• A new dialog box called Macro will appear. Select Hide_Columns_Contain_Even.
• Then, click on the Run button to run this code.

• You will see column H disappear from our dataset.

Hence, we can say that our visual code worked perfectly, and we are able to apply Excel VBA to hide columns based on cell value.

11. Hiding Columns Where Cell Value Is Greater Than a Specific Condition

In this example, we will hide those columns which contain values greater than our desired value. We are going to check if any column contains a value greater than 90. You may notice that column E has a cell value of 100. So, after running the code, this column must disappear.

The steps to complete this example are explained below as follows:

📌 Steps:

• In the beginning, go to the Developer tab and click on Visual Basic. If you don’t have that, you have to enable the Developer tab. You can also press ‘Alt+F11’ to open the Visual Basic Editor.

• As a result, a dialog box will appear.
• Next, in the Insert tab on that box, click Module.

• Write down the following visual code in that empty editor box.

Sub Hide_Columns_Greater_Than_a_Specific_Condition()
    StartRow = 5
    LastRow = 9
    StartCol = 2
    LastCol = 8
    For i = StartRow To LastRow
        For j = StartCol To LastCol
        If IsNumeric(Cells(i, j)) = True Then
            If Cells(i, j).Value > 90 Then
            Columns(j).EntireColumn.Hidden = True
            End If
        End If
        Next j
    Next i
End Sub

• Press ‘Ctrl+S’ to save the code.
• Then, close the Editor tab.
• After that, in the Developer tab, click on Macros, located in group Code.

• A new dialog box called Macro will appear.
• Select Hide_Columns_Greater_Than_a_Specific_Condition and click on the Run button to run this code.

• You will see column E disappears from our dataset.

In the end, we can say that our visual code worked precisely, and we were able to apply Excel VBA to hide columns based on cell value.

12. Hide Columns Where Cell Value Is Less Than a Specific Condition

Similarly, we will hide those columns that contain values less than our desired value. In this case, we are going to check if any column contains a value less than 60. You may also notice that column E has a cell value of 55. So, after running the code, this column must disappear.

The steps to complete this example are given below:

📌 Steps:

• First, go to the Developer tab and click on Visual Basic. If you don’t have that, you have to enable the Developer tab. You can also press ‘Alt+F11’ to open the Visual Basic Editor.

• As a result, a dialog box will appear.
• Now, in the Insert tab on that box, click Module.

• Write down the following visual code in that empty editor box.

Sub Hide_Columns_Less_Than_a_Specific_Condition()
    StartRow = 5
    LastRow = 9
    StartCol = 2
    LastCol = 8
    For i = StartRow To LastRow
        For j = StartCol To LastCol
        If IsNumeric(Cells(i, j)) = True Then
            If Cells(i, j).Value < 60 Then
            Columns(j).EntireColumn.Hidden = True
            End If
        End If
        Next j
    Next i
End Sub

• Press ‘Ctrl+S’ to save the code.
• Afterward, close the Editor tab.
• Then, in the Developer tab, click on Macros, located in group Code.

• A new dialog box called Macro will appear.
• Select Hide_Columns_Less_Than_a_Specific_Condition and click on the Run button to run this code.

• Finally, you will see column E disappears from our dataset.

Lastly, we can say that our visual code worked successfully, and we are able to apply Excel VBA to hide columns based on cell value.

13. Hide Columns Based on Inserted Condition in Cell

In the following example, we are going to write a code in our sheet and input our desired value in a specific cell to hide the column. We will input our condition in cell G11.

The steps to accomplish this example are given below:

📌 Step:

• At first, right-click on the sheet name in the Sheet Name Bar.
• As a result, the Context Menu will appear.
• Now, select the View Code option.

• A white dialog box will appear.
• Then, write down the following visual code in that empty editor box.

Private Sub Worksheet_SelectionChange(ByVal Target As Range)
    StartColumn = 2
    LastColumn = 8
    iRow = 7
    For i = StartColumn To LastColumn
        If Cells(iRow, i).Value = Range("G11").Value Then
            Cells(iRow, i).EntireColumn.Hidden = True
    Else
        Cells(iRow, i).EntireColumn.Hidden = False
        End If
    Next i
End Sub

• Press ‘Ctrl+S’ to save the code.
• After that, close the Editor tab.
• You don’t have to run this code. When you will save the code, it will run automatically.
• Now, If you write down a text or number, that exists in the dataset, you will see the column will disappear.
• To demonstrate it, we write down Biology, and press Enter.

• You will notice that column D will disappear.

Finally, we can say that our visual code worked effectively, and we are able to apply Excel VBA to hide columns based on cell value.

Read More: Excel Hide Columns with No Data 

14. Hide Columns Based on User Input

In this example, we will write a code in our sheet and input a value in a specific cell to hide the column. The value which we have to input is in the merged cell B11. We are going to input our chosen value in cell G11.

The steps to finish this example are given as follows:

📌 Step:

• Firstly, right-click on the sheet name in the Sheet Name Bar.
• As a result, the Context Menu will appear.
• Then, select the View Code option.

• A white dialog box will appear.
• After that, write down the following visual code in that empty editor box.

Private Sub Worksheet_SelectionChange(ByVal Target As Range)
i = Range("G11").Value
Select Case i
Case 1: Columns("B:C").EntireColumn.Hidden = False
Columns("B:C").EntireColumn.Hidden = True
Case 2: Columns("D:E").EntireColumn.Hidden = False
Columns("D:E").EntireColumn.Hidden = True
Case 3: Columns("F:G").EntireColumn.Hidden = False
Columns("F:G").EntireColumn.Hidden = True
Case 4: Columns("H:I").EntireColumn.Hidden = False
Columns("H:I").EntireColumn.Hidden = True
Case Else
Columns("B:I").EntireColumn.Hidden = False
End Select
End Sub

• Press ‘Ctrl+S’ to save the code.
• Finally, close the Editor tab.
• In this case, you don’t have to run this code. When you will save the code, it will run automatically.
• Now, If you input a number according to the list, you will see that two columns will disappear.
• To demonstrate it, we write down 2, and press Enter.

• You will notice columns D and E disappear.

Hence, we can say that our visual code worked precisely, and we are able to apply Excel VBA to hide columns based on cell value.

15. Hide Columns If Cell Value Is Changed

Here, we will change the cell value of our desired cell and observe the change in our dataset.

The steps to this example are shown below:

📌 Step:

• First of all, right-click on the sheet name in the Sheet Name Bar.
• As a result, the Context Menu will appear.
• After that, select the View Code option.

• A white dialog box will appear.
• Write down the following visual code in that empty editor box.

Option Explicit
Private Sub Worksheet_Change(ByVal Target As Range)
    Dim iCell As Range: Set iCell = Me.Range("D5")
    If Intersect(iCell, Target) Is Nothing Then Exit Sub
    If IsNumeric(iCell.Value) Then
        RowHide iCell
    End If
End Sub
Sub RowHide(ByVal SourceCell As Range)
    If SourceCell.Value = 0 Then
        SourceCell.Worksheet.Columns("D:E").Hidden = True
    Else
        SourceCell.Worksheet.Columns("D:E").Hidden = False
    End If
End Sub

• Press ‘Ctrl+S’ to save the code.
• Now, close the Editor tab.
• You don’t have to run this code. When you will save the code, it will run automatically.
• Now, If you change the cell value in cell D5 from Physics to 0, press Enter.

• You will see that columns D and E will disappear.

Finally, we can say that our visual code worked successfully, and we are able to apply Excel VBA to hide columns based on cell value.

Download Practice Workbook

Download this practice workbook for practice while you are reading this article.

Conclusion

That’s the end of this article. I hope that this article will be helpful for you and you will be able to apply Excel VBA to hide columns based on cell value. Please share any further queries or recommendations with us in the comments section below if you have any further questions or recommendations.

Related Articles

• How to Unhide Columns in Excel All at Once
• Unhide Columns in Excel Shortcut Not Working
• 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
• How to Hide and Unhide Columns in Excel