Excel VBA: 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.


Download Practice Workbook

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


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

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.

Hide Columns Based on Cell Text Value with VBA

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. Or You can also press β€˜Alt+F11’ for opening the Visual Basic Editor.

Excel VBA to hide columns based on cell value

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

Excel VBA to hide columns based on cell value

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

Hide Columns Based on Cell Text Value with VBA

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

Hide Columns Based on Cell Text Value with VBA

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.

πŸ”Ž Breakdown of VBA Code

πŸ‘‰ First, provide a name for the sub-procedure which is Hide_Columns_on_Cell_ Text_Value.

πŸ‘‰ Then, we declare the first and last columns of our dataset: StartColumn and LastColumn.

πŸ‘‰ Moreover, we declare the row number through iRow where the text may exist.

πŸ‘‰ After that, we used a VBA For Loop to check from the StartColumn to the LastColumn.

πŸ‘‰ In addition, we used a conditional VBA IF ELSE loop to check our desired value in each cell.

πŸ‘‰ Finally, end the sub-procedure of the code.

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


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. Or You can also press β€˜Alt+F11’ for opening the Visual Basic Editor.

Excel VBA to hide columns based on cell value

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

Excel VBA to hide columns based on cell value

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

Hide Column Based on Cell Numeric Value with VBA

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

Hide Column Based on Cell Numeric Value with VBA

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.

πŸ”Ž Breakdown of VBA Code

πŸ‘‰ First, provide a name for the sub-procedure which is Hide_Columns_on_Cell_ Numeric_Value.

πŸ‘‰ Then, we declare the first and last columns of our dataset: StartColumn and LastColumn.

πŸ‘‰ Moreover, we declare the row number through iRow where the number may exist.

πŸ‘‰ After that, we used a VBA For Loop to check from the StartColumn to the LastColumn.

πŸ‘‰ In addition, we used a conditional VBA IF ELSE loop to check our desired value in each cell.

πŸ‘‰ Finally, end the sub-procedure of the code.

Read More: Excel VBA to Hide Columns Using Column Number (6 Examples)


3. Hide Columns Where Cells Contain Texts

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

Hide Columns Where Cells Contain Texts with VBA

The steps of this example are shown below:

πŸ“Œ Steps:

  • At first, go to the Developer tab and click on Visual Basic. If you don’t have that, you have to enable the Developer tab. Or You can also press β€˜Alt+F11’ for opening the Visual Basic Editor.

Excel VBA to hide columns based on cell value

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

Excel VBA to hide columns based on cell value

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

Hide Columns Where Cells Contain Texts with VBA

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

Hide Columns Where Cells Contain Texts with VBA

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.

πŸ”Ž Breakdown of VBA Code

πŸ‘‰ First, provide a name for the sub-procedure which is Hide_Columns_Contain_Text.

πŸ‘‰ Then, we declare the first and last columns of our dataset: StartRow, LastRow, StartCol, and LastCol.

πŸ‘‰ After that, we used a VBA For Loop to check from the StartRow to LastRow and StartCol to LastCol.

πŸ‘‰ In addition, we used a conditional VBA IF ELSE loop to check our desired requirement in each cell.

πŸ‘‰ Finally, end the sub-procedure of the code.

Read More: How to Hide Columns with Button in Excel (4 Suitable Methods)


4. Hide Columns Where Cells Contain Number

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

Hide Columns Where Cells Contain Number with VBA

The steps of this example are shown as follows:

πŸ“Œ Steps:

  • Firstly, go to the Developer tab and click on Visual Basic. If you don’t have that, you have to enable the Developer tab. Or You can also press β€˜Alt+F11’ for opening the Visual Basic Editor.

Excel VBA to hide columns based on cell value

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

Excel VBA to hide columns based on cell value

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

Hide Columns Where Cells Contain Number with VBA

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

Hide Columns Where Cells Contain Number with VBA

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.

πŸ”Ž Breakdown of VBA Code

πŸ‘‰ First, provide a name for the sub-procedure which is Hide_Columns_Contain _Number.

πŸ‘‰ Then, we declare the first and last columns of our dataset: StartRow, LastRow, StartCol, and LastCol.

πŸ‘‰ After that, we used a VBA For Loop to check from the StartRow to LastRow and StartCol to LastCol.

πŸ‘‰ In addition, we used a conditional VBA IF ELSE loop to check our desired requirement in each cell.

πŸ‘‰ Finally, end the sub-procedure of the code.

Read More: How to Hide Multiple Columns in Excel (5 Easy Methods)


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.

Hide Columns for Zero (0) Cell Value with VBA

The steps of this example are shown as follows:

πŸ“Œ Steps:

  • At the beginning of the process, go to the Developer tab and click on Visual Basic. If you don’t have that, you have to enable the Developer tab. Or You can also press β€˜Alt+F11’ for opening the Visual Basic Editor.

Excel VBA to hide columns based on cell value

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

Excel VBA to hide columns based on cell value

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

Hide Columns for Zero (0) Cell Value with VBA

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

Hide Columns for Zero (0) Cell Value with VBA

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.

πŸ”Ž Breakdown of VBA Code

πŸ‘‰ First, provide a name for the sub-procedure which is Hide_Columns_for_Zore.

πŸ‘‰ Then, we declare the first and last columns of our dataset: StartColumn and LastColumn.

πŸ‘‰ Moreover, we declare the row number through iRow where the number may exist.

πŸ‘‰ After that, we used a VBA For Loop to check from the StartColumn to the LastColumn.

πŸ‘‰ In addition, we used a conditional VBA IF ELSE loop to check the Zero (0) value in each cell.

πŸ‘‰ Finally, end the sub-procedure of the code.

Read More: How to Hide Selected Columns in Excel (5 Easy Methods)


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.

Defining Row Number to Hide Columns with VBA

The steps 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. Or You can also press β€˜Alt+F11’ for opening the Visual Basic Editor.

Excel VBA to hide columns based on cell value

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

Excel VBA to hide columns based on cell value

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

Defining Row Number to Hide Columns with VBA

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

Defining Row Number to Hide Columns with VBA

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.

πŸ”Ž Breakdown of VBA Code

πŸ‘‰ First, provide a name for the sub-procedure which is Hide_Columns_Through_Row_ Number.

πŸ‘‰ Then, we declare a variable.

πŸ‘‰ After that, we used a VBA For Loop to where we mentioned the row number.

πŸ‘‰ In addition, we used a conditional VBA IF ELSE loop to check the cell value B in each cell.

πŸ‘‰ Finally, end the sub-procedure of the code.

Read More: How to Hide Rows and Columns in Excel (10 Ways)


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.

Hide Columns Based on Cell’s Negative Value with VBA

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

πŸ“Œ 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. Or You can also press β€˜Alt+F11’ for opening the Visual Basic Editor.

Excel VBA to hide columns based on cell value

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

Excel VBA to hide columns based on cell value

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

Hide Columns Based on Cell’s Negative Value with VBA

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

Hide Columns Based on Cell’s Negative Value with VBA

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.

πŸ”Ž Breakdown of VBA Code

πŸ‘‰ First, provide a name for the sub-procedure which is Hide_Columns_Contain_ Negative.

πŸ‘‰ Then, we declare the first and last columns of our dataset: StartColumn and LastColumn.

πŸ‘‰ Moreover, we declare the row number through iRow where the number may exist.

πŸ‘‰ After that, we used a VBA For Loop to check from the StartColumn to the LastColumn.

πŸ‘‰ In addition, we used a conditional VBA IF ELSE loop to check the value less than Zero (0) or a negative number in each cell.

πŸ‘‰ Finally, end the sub-procedure of the code.

Read More: How to Hide Columns in Excel with Minus or Plus Sign (2 Quick Ways)


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.

Hide Columns Based on Cell’s Positive Value with VBA

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. Or You can also press β€˜Alt+F11’ for opening the Visual Basic Editor.

Excel VBA to hide columns based on cell value

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

Excel VBA to hide columns based on cell value

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

Hide Columns Based on Cell’s Positive Value with VBA

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

Hide Columns Based on Cell’s Positive Value with VBA

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.

πŸ”Ž Breakdown of VBA Code

πŸ‘‰ First, provide a name for the sub-procedure which is Hide_Columns_Contain_ Positive.

πŸ‘‰ Then, we declare the first and last columns of our dataset: StartRow, LastRow, StartCol, and LastCol.

πŸ‘‰ After that, we used a VBA For Loop to check from the StartRow to LastRow and StartCol to LastCol.

πŸ‘‰ In addition, we used a conditional VBA IF ELSE loop to check the positive value in each cell.

πŸ‘‰ Finally, end the sub-procedure of the code.


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.

Hide Columns Where Cells Contain Odd Numbers with VBA

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

πŸ“Œ Steps:

  • At first, go to the Developer tab and click on Visual Basic. If you don’t have that, you have to enable the Developer tab. Or You can also press β€˜Alt+F11’ for opening the Visual Basic Editor.

Excel VBA to hide columns based on cell value

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

Excel VBA to hide columns based on cell value

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

Hide Columns Where Cells Contain Odd Numbers with VBA

  • You will see column E disappears from our dataset.

Hide Columns Where Cells Contain Odd Numbers with VBA

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.

πŸ”Ž Breakdown of VBA Code

πŸ‘‰ First, provide a name for the sub-procedure which is Hide_Columns_Contain_Odd.

πŸ‘‰ Then, we declare the first and last columns of our dataset: StartRow, LastRow, StartCol, and LastCol.

πŸ‘‰ After that, we used a VBA For Loop to check from the StartRow to LastRow and StartCol to LastCol.

πŸ‘‰ In addition, we used a conditional VBA IF ELSE loop to check whether the value is odd or even in each cell.

πŸ‘‰ Finally, end the sub-procedure of the code.

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.

Hide Columns Where Cells Contain Even Numbers with VBA

The steps to complete this example are given below:

πŸ“Œ Steps:

  • Firstly, go to the Developer tab and click on Visual Basic. If you don’t have that, you have to enable the Developer tab. Or You can also press β€˜Alt+F11’ for opening the Visual Basic Editor.

Excel VBA to hide columns based on cell value

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

Excel VBA to hide columns based on cell value

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

Hide Columns Where Cells Contain Even Numbers with VBA

  • You will see column H disappear from our dataset.

Hide Columns Where Cells Contain Even Numbers with VBA

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.

πŸ”Ž Breakdown of VBA Code

πŸ‘‰ First, provide a name for the sub-procedure which is Hide_Columns_Contain_Even.

πŸ‘‰ Then, we declare the first and last columns of our dataset: StartRow, LastRow, StartCol, and LastCol.

πŸ‘‰ After that, we used a VBA For Loop to check from the StartRow to LastRow and StartCol to LastCol.

πŸ‘‰ In addition, we used a conditional VBA IF ELSE loop to check whether the value is odd or even in each cell.

πŸ‘‰ Finally, end the sub-procedure of the code.


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.

Hiding Columns Where Cell Value Is Greater Than a Specific Condition with VBA

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. Or You can also press β€˜Alt+F11’ for opening the Visual Basic Editor.

Excel VBA to hide columns based on cell value

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

Excel VBA to hide columns based on cell value

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

Hiding Columns Where Cell Value Is Greater Than a Specific Condition with VBA

  • You will see column E disappears from our dataset.

Hiding Columns Where Cell Value Is Greater Than a Specific Condition with VBA

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

πŸ”Ž Breakdown of VBA Code

πŸ‘‰ First, provide a name for the sub-procedure which is Hide_Columns_Greater_ Than_a_Specific_Condition.

πŸ‘‰ Then, we declare the first and last columns of our dataset: StartRow, LastRow, StartCol, and LastCol.

πŸ‘‰ After that, we used a VBA For Loop to check from the StartRow to LastRow and StartCol to LastCol.

πŸ‘‰ In addition, we used a conditional VBA IF ELSE loop to check whether the value is greater than 90 in each cell.

πŸ‘‰ Finally, end the sub-procedure of the code.


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

Similarly, we will hide those columns which 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.

Hide Columns Where Cell Value Is Less Than a Specific Condition with VBA

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. Or You can also press β€˜Alt+F11’ for opening the Visual Basic Editor.

Excel VBA to hide columns based on cell value

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

Excel VBA to hide columns based on cell value

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

Hide Columns Where Cell Value Is Less Than a Specific Condition with VBA

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

Hide Columns Where Cell Value Is Less Than a Specific Condition with VBA

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.

πŸ”Ž Breakdown of VBA Code

πŸ‘‰ First, provide a name for the sub-procedure which is Hide_Columns_Less _Than_a_Specific_Condition.

πŸ‘‰ Then, we declare the first and last columns of our dataset: StartRow, LastRow, StartCol, and LastCol.

πŸ‘‰ After that, we used a VBA For Loop to check from the StartRow to LastRow and StartCol to LastCol.

πŸ‘‰ In addition, we used a conditional VBA IF ELSE loop to check whether the value is less than 60 in each cell.

πŸ‘‰ Finally, end the sub-procedure of the code.


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.

Hide Columns Based on Inserted Condition in Cell with VBA

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

Hide Columns Based on Inserted Condition in Cell with VBA

  • You will notice that column D will disappear.

Hide Columns Based on Inserted Condition in Cell with VBA

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.

πŸ”Ž Breakdown of VBA Code

πŸ‘‰ First, initiating an event under the specific worksheet where Target is passed as an argument with Range type. When the Range changes, the event occurs.

πŸ‘‰ Then, we declare the first and last columns of our dataset: StartColumn and LastColumn.

πŸ‘‰ Moreover, we declare the row number through iRow where the text may exist.

πŸ‘‰ After that, we used a VBA For Loop to check from the StartColumn to the LastColumn.

πŸ‘‰ We used a conditional VBA IF ELSE loop to check our desired value in each cell. In addition, we also mentioned the input cell range.

πŸ‘‰ Finally, end the sub-procedure of the code.

Read More: Excel Hide Columns with No Data (4 Effective Ways)


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.

Hide Columns Based on User Input with VBA

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

Hide Columns Based on User Input with VBA

  • You will notice columns D and E disappeared.

Hide Columns Based on User Input with VBA

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.

πŸ”Ž Breakdown of VBA Code

πŸ‘‰ First, initiating an event under the specific worksheet where Target is passed as an argument with Range type. When the Range changes, the event occurs.

πŸ‘‰ Then, we declare the cell range where the user inputs the value.

πŸ‘‰ We used a conditional VBA IF ELSE loop to check for each user input and show the result.

πŸ‘‰ Finally, end the sub-procedure of the code.


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.

Hide Columns If Cell Value Is Changed with VBA

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

Hide Columns If Cell Value Is Changed with VBA

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

Hide Columns If Cell Value Is Changed with VBA

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.

πŸ”Ž Breakdown of VBA Code

πŸ‘‰ First, we were forced to declare all variables.

πŸ‘‰ Then, initiating an event under the specific worksheet where Target passed as an argument with Range type. When the Range changes, the event occurs.

πŸ‘‰ After that, we declared where we will change the value.

πŸ‘‰ End the first sub-procedure.

πŸ‘‰ Next, Initiate the sub-procedure of the ColumnHide macro. Variable SourceCell passed as a Range type argument.

πŸ‘‰ We used a conditional VBA IF ELSE loop to hide columns β€œD:E” if the Zero (0) value is imputed in cell D5.

πŸ‘‰ Finally, end the sub-procedure of the code.


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.

Don’t forget to check our website ExcelDemy for several Excel-related problems and solutions. Keep learning new methods and keep growing!


Related Articles

Soumik Dutta

Soumik Dutta

Hi! I am Soumik. I have completed my Bachelor of Science in Naval Architecture and Marine Engineering. I have found my calling in Machine Learning and Data Science, and in pursuing so, I have realized the importance of Data Analysis. Excel is one excellent tool to fulfill the purpose. I am always trying to learn every day about different features of Excel and trying to share here what I am learning.

We will be happy to hear your thoughts

Leave a reply

ExcelDemy
Logo