unhide hidden row

carlandtina02 · #1 03-09-2016, 07:15 AM

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

Code:

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 · #2 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

carlandtina02 · #3 03-11-2016, 04:52 AM

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 · #4 03-11-2016, 03:08 PM

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 · #5 03-11-2016, 03:29 PM

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 · #6 03-11-2016, 03:33 PM

Haha,

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

Carl

Philb1 · #7 03-11-2016, 08:06 PM

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 · #8 03-14-2016, 02:26 PM

Hi Phil,

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

Regards
Carl

Similar Threads
Thread	Thread Starter	Forum	Replies	Last Post
A row that will not UNHIDE	MaineLady	Excel	0	11-23-2014 01:15 PM
VBA to keep hidden rows hidden when filtering	tectonicseer	Excel Programming	1	07-27-2014 11:54 AM
Unhide Projects in VBA Editor	Greg S.	Excel Programming	2	07-24-2013 07:31 AM
Hide/Unhide pictures	maloneb	PowerPoint	1	01-18-2012 03:34 PM
Hidden style applied over already-hidden text.	christie	Word	1	08-17-2011 09:10 AM