Need Help with VBA Code to Hide/Unhide Rows

808excel

New Member
Joined
Oct 4, 2019
Messages
10
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

Well-known Member
Joined
Aug 31, 2016
Messages
2,906
Code:
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

New Member
Joined
Oct 4, 2019
Messages
10
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

Well-known Member
Joined
Feb 1, 2016
Messages
2,241
Office Version
2013
Platform
Windows
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.

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
 
Last edited:

808excel

New Member
Joined
Oct 4, 2019
Messages
10
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.

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

Well-known Member
Joined
Feb 1, 2016
Messages
2,241
Office Version
2013
Platform
Windows
I don't understand, it works for me.
Maybe the Application.EnableEvents got turn off?
Run this sub first to turn it on.
Code:
Sub toEvent()
Application.EnableEvents = True
End Sub
 

808excel

New Member
Joined
Oct 4, 2019
Messages
10
I'll try and check and report back on Monday. Thanks again for the help!
 

808excel

New Member
Joined
Oct 4, 2019
Messages
10
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

Well-known Member
Joined
Feb 1, 2016
Messages
2,241
Office Version
2013
Platform
Windows
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

New Member
Joined
Oct 4, 2019
Messages
10
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
 

Forum statistics

Threads
1,077,666
Messages
5,335,575
Members
399,026
Latest member
Im_Stupid

Some videos you may like

This Week's Hot Topics

Top