OzGrid

How to hide rows based on 2 values

Requirement:

The user has a need to hide rows based on 2 values. The first value is the trigger Y or N (to hide or not hide), and the second value is if a value in each row is equal to zero. So if the trigger value is Y AND the end value in a row = 0, then hide the row. If the AND test fails, do not hide the row.

The code the user currently has now is:

Option Explicit
Private Sub Worksheet_Change(ByVal Target As Range)
If Target.Address = "$R$5" Then
If Range("R5").Value = "Y" Then 'need the syntax for AND (Column T, Row number). Do next line of code when ($R$5 = Y AND cell (T,rows 9 to 16) =0), then
Rows("9:16").EntireRow.Hidden = True
ElseIf Range("R5").Value = "N" Then
Rows("9:16").EntireRow.Hidden = False
End If
End If
End Sub

https://www.ozgrid.com/forum/forum/help-forums/excel-vba-macros/1201292-hide-rows-based-on-2-values

Solution:

Option Explicit
Private Sub Worksheet_Change(ByVal Target As Range)

    Dim lngMyRow As Long

    With Application
        .ScreenUpdating = False
        .EnableEvents = False
    End With

    If Target.Address = "$R$5" Then
        If Range("R5").Value = "Y" Then
            Rows("9:16").EntireRow.Hidden = False
            For lngMyRow = 16 To 9 Step -1
                If Val(Range("T" & lngMyRow)) = 0 Then 'Also includes blanks
                    Rows(lngMyRow).EntireRow.Hidden = True
                End If
            Next lngMyRow
        ElseIf Range("R5").Value = "N" Then
            Rows("9:16").EntireRow.Hidden = False
        End If
    End If
    
    With Application
        .EnableEvents = True
        .ScreenUpdating = True
    End With
    
End Sub

Obtained from the OzGrid Help Forum.

Solution provided by Trebor76.

See also: Index to Excel VBA Code and Index to Excel Freebies and Lesson 1 - Excel Fundamentals and Index to how to… providing a range of solutions and Index to new resources and reference sheets

See also:

Click here to visit our Free 24/7 Excel/VBA Help Forum where there are thousands of posts you can get information from, or you can join the Forum and post your own questions.

Gallery