unhide hidden row

[carlandtina02]

Hi Folks,

Can anyone help with this?

The current code hides rows E33:E58 and unhides one row at a time if the previous row is populated and works great, but now i need it to do the same to another set of rows too (rows E61:E86), i thought i would be able to copy the code and just change the cell address but no joy! any help would be great )



Thanks in advance

Private Sub Worksheet_Change(ByVal Target As Range)
On Error Resume Next
If Not Intersect(Target, Range("E33:E58")) Is Nothing Then
Application.ScreenUpdating = False
Dim LR As Long
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
End If

End Sub] 

[Philb1]

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

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

[carlandtina02]

Hi Philb1,

That works great, just one thing though, the rows only hide upon data entry, is there a condition to hide them before data entry? (I've attached a workbook for reference).

Thanks for your help, it's much apreciated

Regards

Carlandtina02

[Philb1]

I carlandtina
I don't fully understand what you need. Do you mean you want the lines E33:E58 and E61:E86 hidden? So when you look at the sheet those lines are not visible? If E58 or E86 is empty the lines for that table are hidden? I'm not sure how things are supposed to work lol. If you can tell me in laymans terms I'd appreciate it
Cheers
Phil

[carlandtina02]

Hi Phil,

Haha, sorry its probably me not explaining properly!

What i want to happen is for rows 33 and 61 to always be visible with the rest hidden, then when one of the above rows is populated it will unhide the next row ready for populating.

Hope this helps

Thanks
Carl

[carlandtina02]

Haha,

Phil i forgot to mention that i want this to happen after i press the clear report button!

Carl

[Philb1]

Hi Carl

I changed the ClearReport macro from just deleting the contents of the service report sheet to include the hiding of the rows.
I changed the worksheet change macro by removing & changing some of the code.

I hope it's what you're after
Cheers
Phil

[carlandtina02]

Hi Phil,

That works great now, thanks very much for your help, you're a star!!!


Regards
Carl