How to Use VBA Code to Hide or Unhide Rows Based on Formula Values in Excel

Sometimes, you may want to hide or unhide some rows in your worksheet based on the value of a certain cell. For example, you may want to hide the rows that contain blank values, or show the rows that meet a specific condition. This can make your worksheet more organized and easier to read.

One way to achieve this is to use the built-in filter feature in Excel, which allows you to filter the data by criteria and hide the rows that do not match. However, this method requires manual intervention and may not be updated automatically when the data changes.

Another way to achieve this is to use Visual Basic for Applications (VBA) code, which is a programming language that can automate tasks in Excel. With VBA code, you can write a macro that can hide or unhide rows based on the value of a cell, and run it automatically whenever the value changes. This way, you can save time and ensure that your worksheet always reflects the latest data.

In this article, we will show you how to write and use VBA code to hide or unhide rows based on value through excel formula. We will also provide a detailed explanation of the code and a scenario to illustrate the application of the code.

The basic idea of hiding or un-hiding rows based on value through VBA code is to use the Hidden property of the Range object, which represents a cell or a range of cells in Excel. The Hidden property can be set to True or False to hide or unhide the rows that contain the range.

To access the Hidden property, we need to specify the range that we want to hide or unhide. For example, if we want to hide or unhide row 10, we can use Rows("10:10") to refer to the entire row 10. Alternatively, we can use Range("A10").EntireRow to refer to the same row, starting from column A.

To hide or unhide multiple rows, we can use a comma to separate the ranges. For example, if we want to hide or unhide rows 10 to 15, we can use Rows("10:15") or Range("A10:A15").EntireRow to refer to the range of rows.

To hide or unhide rows based on the value of a cell, we need to use an If statement to check the condition of the cell. For example, if we want to hide row 10 if the value of cell A10 is “Passed”, we can use the following code:

If Range("A10").Value = "Passed" Then
    Rows("10:10").Hidden = True
End If

To unhide row 10 if the value of cell A10 is “Failed”, we can use the following code:

If Range("A10").Value = "Failed" Then
    Rows("10:10").Hidden = False
End If

To hide or unhide rows based on the value of a cell that contains a formula, we need to use the Value property of the Range object, which returns the result of the formula. For example, if cell A10 contains the formula =IF(B10>100,"Passed","Failed"), we can use the same code as above to hide or unhide row 10 based on the value of cell A10.

To run the code automatically whenever the value of the cell changes, we need to use the Worksheet_Change event, which is triggered when a cell or a range of cells on the worksheet is changed by the user or by an external link. The Worksheet_Change event takes a parameter called Target, which represents the range that has been changed.

To use the Worksheet_Change event, we need to place the code in the worksheet module, which is the code window that corresponds to the worksheet that contains the data. To access the worksheet module, we can right-click on the worksheet tab and select “View Code” from the context menu.

In the worksheet module, we can write the code for the Worksheet_Change event as follows:

Private Sub Worksheet_Change(ByVal Target As Range)
    'code to hide or unhide rows based on value
End Sub

To avoid running the code when the change is not related to the cell that we want to monitor, we can use the Intersect function to check if the Target range overlaps with the cell or the range of cells that we want to monitor. For example, if we want to monitor the value of cell A10, we can use the following code:

Private Sub Worksheet_Change(ByVal Target As Range)
    If Not Intersect(Target, Range("A10")) Is Nothing Then
        'code to hide or unhide rows based on value
    End If
End Sub

The Intersect function returns the range that is the intersection of two or more ranges. If the ranges do not intersect, it returns Nothing. Therefore, the If statement above will only run the code if the Target range intersects with the range A10.

Procedures

To write and use VBA code to hide or unhide rows based on value through excel formula, we can follow these steps:

1. Open the worksheet that contains the data and the formula that we want to monitor.
2. Right-click on the worksheet tab and select “View Code” from the context menu. This will open the Visual Basic Editor and the worksheet module.
3. In the worksheet module, write the code for the Worksheet_Change event, using the Hidden property, the If statement, and the Intersect function, as explained in the previous section. For example, if we want to hide or unhide rows 10 to 15 based on the value of cell A10, which contains the formula =IF(B10>100,"Passed","Failed"), we can use the following code:

Private Sub Worksheet_Change(ByVal Target As Range)
    If Not Intersect(Target, Range("A10")) Is Nothing Then
        If Range("A10").Value = "Passed" Then
            Rows("10:15").Hidden = True
        ElseIf Range("A10").Value = "Failed" Then
            Rows("10:15").Hidden = False
        End If
    End If
End Sub

4. Save and close the Visual Basic Editor. Return to the worksheet and test the code by changing the value of cell B10 and see how the rows 10 to 15 are hidden or unhidden accordingly.

Explanation

The code that we wrote in the previous section can be explained as follows:

• The first line Private Sub Worksheet_Change(ByVal Target As Range) declares the Worksheet_Change event, which takes a parameter called Target, which represents the range that has been changed on the worksheet.
• The second line If Not Intersect(Target, Range("A10")) Is Nothing Then checks if the Target range overlaps with the range A10, which is the cell that we want to monitor. If the ranges do not intersect, the code will exit the If statement and do nothing. If the ranges do intersect, the code will continue to the next line.
• The third line If Range("A10").Value = "Passed" Then checks if the value of cell A10 is “Passed”. If it is, the code will execute the next line. If it is not, the code will skip to the next ElseIf statement.
• The fourth line Rows("10:15").Hidden = True sets the Hidden property of the range Rows("10:15") to True, which means that the rows 10 to 15 will be hidden.
• The fifth line ElseIf Range("A10").Value = "Failed" Then checks if the value of cell A10 is “Failed”. If it is, the code will execute the next line. If it is not, the code will skip to the next Else statement or the End If statement.
• The sixth line Rows("10:15").Hidden = False sets the Hidden property of the range Rows("10:15") to False, which means that the rows 10 to 15 will be unhidden.
• The seventh line End If ends the If statement that checks the value of cell A10.
• The eighth line End If ends the If statement that checks the intersection of the Target range and the range A10.
• The ninth line End Sub ends the Worksheet_Change event.

Scenario

To illustrate the application of the code, let us consider a scenario where we have a worksheet that contains the sales data of a company. The worksheet has the following columns:

• A: Product Name
• B: Sales Amount
• C: Sales Target
• D: Status

The status column contains a formula that compares the sales amount and the sales target, and returns “Passed” if the sales amount is greater than or equal to the sales target, and “Failed” otherwise. The formula is =IF(B2>=C2,"Passed","Failed"), and it is copied down to the rest of the column.

We want to hide the rows that have the status “Passed”, and show the rows

that have the status “Failed”, so that we can focus on the products that need improvement. To do this, we can use the VBA code that we wrote in the previous section, with some modifications.

First, we need to change the range that we want to monitor from A10 to D2:D11, which is the range that contains the status column. Second, we need to change the range that we want to hide or unhide from Rows("10:15") to Target.EntireRow, which means that the code will hide or unhide the entire row that corresponds to the changed cell. Third, we need to use a For Each loop to iterate over each cell in the Target range, in case the change affects more than one cell.

The modified code is as follows:

Private Sub Worksheet_Change(ByVal Target As Range)
    If Not Intersect(Target, Range("D2:D11")) Is Nothing Then
        Dim Cell As Range
        For Each Cell In Intersect(Target, Range("D2:D11"))
            If Cell.Value = "Passed" Then
                Cell.EntireRow.Hidden = True
            ElseIf Cell.Value = "Failed" Then
                Cell.EntireRow.Hidden = False
            End If
        Next Cell
    End If
End Sub

The explanation of the modified code is as follows:

• The first line Private Sub Worksheet_Change(ByVal Target As Range) declares the Worksheet_Change event, which takes a parameter called Target, which represents the range that has been changed on the worksheet.
• The second line If Not Intersect(Target, Range("D2:D11")) Is Nothing Then checks if the Target range overlaps with the range D2:D11, which is the range that contains the status column. If the ranges do not intersect, the code will exit the If statement and do nothing. If the ranges do intersect, the code will continue to the next line.
• The third line Dim Cell As Range declares a variable called Cell, which will be used to store each cell in the Target range that intersects with the range D2:D11.
• The fourth line For Each Cell In Intersect(Target, Range("D2:D11")) starts a For Each loop, which will iterate over each cell in the range that is the intersection of the Target range and the range D2:D11.
• The fifth line If Cell.Value = "Passed" Then checks if the value of the current cell is “Passed”. If it is, the code will execute the next line. If it is not, the code will skip to the next ElseIf statement.
• The sixth line Cell.EntireRow.Hidden = True sets the Hidden property of the entire row that contains the current cell to True, which means that the row will be hidden.
• The seventh line ElseIf Cell.Value = "Failed" Then checks if the value of the current cell is “Failed”. If it is, the code will execute the next line. If it is not, the code will skip to the next Else statement or the Next statement.
• The eighth line Cell.EntireRow.Hidden = False sets the Hidden property of the entire row that contains the current cell to False, which means that the row will be unhidden.
• The ninth line Next Cell ends the For Each loop and moves to the next cell in the range.
• The tenth line End If ends the If statement that checks the intersection of the Target range and the range D2:D11.
• The eleventh line End Sub ends the Worksheet_Change event.

Excel Table

To demonstrate the effect of the code, we can use the following excel table as an example:

After applying the code, the rows that have the status “Passed” will be hidden, and the rows that have the status “Failed” will be shown. The table will look like this:

Result

The result of the scenario is that we can hide or unhide rows based on the value of the status column, which is calculated by a formula that compares the sales amount and the sales target. This way, we can focus on the products that need improvement and ignore the products that have met or exceeded the sales target.

Other Approaches

Besides using VBA code, there are other approaches to hide or unhide rows based on value through excel formula. Some of them are:

• Using conditional formatting and a custom number format. This method involves applying a conditional formatting rule to the rows that we want to hide or unhide, and using a custom number format that makes the text invisible. For example, we can use the custom number format ;;; to hide the text, and the default number format General to show the text. The advantage of this method is that it does not require any coding, and it can be easily applied and modified. The disadvantage of this method is that it does not actually hide the rows, but only makes them appear blank. Therefore, the row height and the row numbers will still be visible, and the hidden rows will still be counted in formulas and charts.
• Using a helper column and a filter. This method involves adding a helper column that contains a formula that returns a value based on the condition that we want to use to hide or unhide the rows. For example, we can use the formula =IF(D2="Passed",1,0) to return 1 if the status is “Passed”, and 0 otherwise. Then, we can apply a filter to the helper column and filter out the values that we want to hide. For example, we can filter out the value 1 to hide the rows that have the status “Passed”. The advantage of this method is that it does not require any coding, and it can be easily applied and modified. The disadvantage of this method is that it requires an extra column, and it may not be updated automatically when the data changes.