Need Help with VBA Code to Hide/Unhide Rows

[808excel]

Hi,

I have the following code, but how can I apply this to rows below A5 to act in the same way? For example, I need this to be applied to A5:A44 and then A48:A87 (possibly for more rows below so I need the code to only perform the hide/unhide within the specified areas). Also, is it possible to apply this to a cell from another sheet?

Private Sub Worksheet_Change(ByVal Target As Range)
On Error GoTo ExitSub
If Intersect(Target, Me.Range("A5")) Is Nothing Then Exit Sub
Application.EnableEvents = False
If Target <> "" Then
Rows("6").EntireRow.Hidden = False
Else
Rows("6").EntireRow.Hidden = True
End If

ExitSub:
Application.EnableEvents = True
End Sub


Thanks!

 

[Logit]

Private Sub Worksheet_Change(ByVal Target As Range)
On Error GoTo ExitSub
If Intersect(Target, Me.Range("A5")) Is Nothing Then Exit Sub
Application.EnableEvents = False
If Target <> "" Then
Rows("6").EntireRow.Hidden = False
Else
Rows("6:44").EntireRow.Hidden = True
End If


ExitSub:
Application.EnableEvents = True
End Sub

 

[808excel]

Thanks, but I need it to work the same way that the code below does, but I was hoping that this could be written in a way that isn't so labor intensive and extensive.

If Range("A5").Value = "" Then
Rows("6").EntireRow.Hidden = True
Else
Rows("6").EntireRow.Hidden = False
End If
If Range("A6").Value = "" Then
Rows("7").EntireRow.Hidden = True
Else
Rows("7").EntireRow.Hidden = False
End If
If Range("A7").Value = "" Then
Rows("8").EntireRow.Hidden = True
Else
Rows("8").EntireRow.Hidden = False
End If
If Range("A8").Value = "" Then
Rows("9").EntireRow.Hidden = True
Else
Rows("9").EntireRow.Hidden = False
End If
If Range("A9").Value = "" Then
Rows("10").EntireRow.Hidden = True
Else
Rows("10").EntireRow.Hidden = False
End If

 

[Akuini]

Try this:
But the code only deals with change in a single cell at a time.
If you need it to deal also with change in multiple cells at a time, like when you copy paste some cells to the target range, then you need another code.

Private Sub Worksheet_Change(ByVal Target As Range)
On Error GoTo ExitSub
If Target.Cells.CountLarge <> 1 Then Exit Sub
If Intersect(Target, Range("A5:A44, A48:A87")) Is Nothing Then Exit Sub
Application.EnableEvents = False
Target.Offset(1).EntireRow.Hidden = Target = ""

ExitSub:
Application.EnableEvents = True
End Sub

 

[808excel]

Try this:
But the code only deals with change in a single cell at a time.
If you need it to deal also with change in multiple cells at a time, like when you copy paste some cells to the target range, then you need another code.

Private Sub Worksheet_Change(ByVal Target As Range)
On Error GoTo ExitSub
If Target.Cells.CountLarge <> 1 Then Exit Sub
If Intersect(Target, Range("A5:A44, A48:A87")) Is Nothing Then Exit Sub
Application.EnableEvents = False
Target.Offset(1).EntireRow.Hidden = Target = ""

ExitSub:
Application.EnableEvents = True
End Sub

I tried this and it only works for cell A5. When I enter in cell A6 it doesn't unhide cell A7.

 

[Akuini]

I don't understand, it works for me.
Maybe the Application.EnableEvents got turn off?
Run this sub first to turn it on.

Sub toEvent()
Application.EnableEvents = True
End Sub

 

[808excel]

I'll try and check and report back on Monday. Thanks again for the help!

 

[808excel]

Hi Akuini. THANKS! I tried what you instructed and it now works perfect! How would my application.enableevents get turned off? If I wanted to add more areas to hide would I just add to the vba line as shown below?
If Intersect(Target, Range("A5:A44, A48:A87, A90:A95"))

 

[Akuini]

How would my application.enableevents get turned off?

Usually when this line is executed: Application.EnableEvents = False
but then somehow there's an error so it doesn't reach this line: Application.EnableEvents = True

If I wanted to add more areas to hide would I just add to the vba line as shown below?
If Intersect(Target, Range("A5:A44, A48:A87, A90:A95"))

Yes

 

[808excel]

Thanks again. Can you help me combine the code you provided with the following code too? I tried a couple of way with no luck as I'm new to doing this.

If Intersect(Target, Me.Range("B14")) Is Nothing Then Exit Sub
Application.EnableEvents = False
If Target = "Yes" Then
Worksheets("sheet2").Rows("138").EntireRow.Hidden = True
Worksheets("sheet2").Rows("139:140").EntireRow.Hidden = False
Else
Worksheets("sheet2").Rows("138").EntireRow.Hidden = False
Worksheets("sheet2").Rows("139:140").EntireRow.Hidden = True
End If