Hiding Rows Based on a Cell Value

Excel provides conditional formatting which allows you to change the color and other attributes of a cell based on the content of the cell. There is no way, unfortunately, to easily hide rows based on the value of a particular cell in a row. You can, however, achieve the same effect by using a macro to analyze the cell and adjust row height accordingly. The following macro will examine a particular cell in the first 100 rows of a worksheet, and then hide the row if the value in the cell is less than 5.

Sub HideRows()
    BeginRow = 1
    EndRow = 100
    ChkCol = 3

    For RowCnt = BeginRow To EndRow
        If Cells(RowCnt, ChkCol).Value < 5 Then
            Cells(RowCnt, ChkCol).EntireRow.Hidden = True
        End If
    Next RowCnt
End Sub

You can modify the macro so that it checks a different beginning row, ending row, and column by simply changing the first three variables set in the macro. You can also easily change the value that is checked for within the For ... Next loop.

You should note that this macro doesn't unhide any rows, it simply hides them. If you are checking the contents of a cell that can change, you may want to modify the macro a bit so that it will either hide or unhide a row, as necessary. The following variation will do the trick:

Sub HURows()
    BeginRow = 1
    EndRow = 100
    ChkCol = 3

    For RowCnt = BeginRow To EndRow
        If Cells(RowCnt, ChkCol).Value < 5 Then
            Cells(RowCnt, ChkCol).EntireRow.Hidden = True
        Else
            Cells(RowCnt, ChkCol).EntireRow.Hidden = False
        End If
    Next RowCnt
End Sub