Need Help with VBA Code to Hide/Unhide Rows

808excel

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

Excel Facts

VLOOKUP to Left?
Use =VLOOKUP(A2,CHOOSE({1,2},$Z$1:$Z$99,$Y$1:$Y$99),2,False) to lookup Y values to left of Z values.
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
 
Upvote 0
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
 
Upvote 0
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:
Upvote 0
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.
 
Upvote 0
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
 
Upvote 0
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"))
 
Upvote 0
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
 
Upvote 0
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
 
Upvote 0

Forum statistics

Threads
1,213,528
Messages
6,114,154
Members
448,553
Latest member
slaytonpa

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Back
Top