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.

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

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.

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

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.

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

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.

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

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.

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

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.

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

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.

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

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.

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

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.

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

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.

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

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.

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

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

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.

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

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.

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

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.

🔎 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 would 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.

Download Practice Workbook

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

Hide Columns Based on Cell Value.xlsm

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.

How to Unhide All Columns with Excel VBA

Get FREE Advanced Excel Exercises with Solutions!

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

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

1. Hide Columns Based on Cell Text Value with VBA

2. Hide Column Based on Cell Numeric Value

3. Hide Columns Where Cells Contain Texts

4. Hide Columns Where Cells Contain Number

5. Hide Columns for Zero (0) Cell Value

6. Defining Row Number to Hide Columns

7. Hide Columns Based on Cell’s Negative Value

8. Hide Columns Based on Cell’s Positive Value

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

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

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

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

13. Hide Columns Based on Inserted Condition in Cell

14. Hide Columns Based on User Input

15. Hide Columns If Cell Value Is Changed

Conclusion

Related Article

Leave a reply Cancel reply