Microsoft Office Forums - View Single Post

Philb1 · 03-10-2016, 08:01 PM

Try this code. If it works as it was for one range then this should work for two ranges.
I can sort of see what you're trying to do. Try it & see if it's what you want.
Post a sample workbook if you need further assistance
Cheers

Code:

Option Explicit
Private Sub Worksheet_Change(ByVal Target As Range)

Dim LR As Long

On Error GoTo ExitOut

If Not Intersect(Target, Range("E33:E58")) Is Nothing Then
    With Application
        .EnableEvents = False
        .ScreenUpdating = False
    End With
    
    LR = Range("E58").End(xlUp).Row
    
    If Not IsEmpty(Range("E58")) Then
        Range("E33:E58").EntireRow.Hidden = False
    Exit Sub
    End If
    
    If Range("E58").EntireRow.Hidden = False Then
        Range("E" & LR + 2, "E58").EntireRow.Hidden = True
    Else
        If Not IsEmpty(Target.Offset(1, 0)) Then
            Target.End(xlDown).Offset(1, 0).EntireRow.Hidden = False
        Else
            Target.Offset(1, 0).EntireRow.Hidden = False
        End If
    End If
    GoTo ExitOut
    
ElseIf Not Intersect(Target, Range("E61:E86")) Is Nothing Then
    With Application
        .EnableEvents = False
        .ScreenUpdating = False
    End With

    LR = Range("E86").End(xlUp).Row
    
    If Not IsEmpty(Range("E86")) Then
        Range("E61:E86").EntireRow.Hidden = False
    Exit Sub
    End If
    
    If Range("E86").EntireRow.Hidden = False Then
        Range("E" & LR + 2, "E86").EntireRow.Hidden = True
    Else
        If Not IsEmpty(Target.Offset(1, 0)) Then
            Target.End(xlDown).Offset(1, 0).EntireRow.Hidden = False
        Else
            Target.Offset(1, 0).EntireRow.Hidden = False
        End If
    End If
    GoTo ExitOut
End If

ExitOut:
With Application
    .EnableEvents = True
    .ScreenUpdating = True
End With
Exit Sub
End Sub