Hide/Unhide Rows Based on Cell Data

JQB87

New Member
Joined
Oct 19, 2020
Messages
6
Office Version
  1. 365
Platform
  1. Windows
Hello, I'm looking to hide and unhide rows on a sheet based on input values on a specific cell, including blanks. I don't have any experience in using Codes and tried a couple from other posts here that didn't quite match what I needed. I'm using excel to gather survey information so there are multiple rows/sections that I want to hide when someone chooses N within the main header row.
I'm using data validation allowing these options to be selected (blank/Y/N), when blank and N are chosen I need rows to be hidden but when Y is chosen I need the rows to unhide. There are 11 main headers that people will be required to say Y/N on or leave blank.
I tried using this below:
VBA Code:
Private Sub Worksheet_Change(ByVal Target As Range)
    '   Code goes in the Worksheet specific module
    Dim rng As Range
        '   Set Target Range, i.e. Range("A1, B2, C3"), or Range("A1:B3")
        Set rng = Target.Parent.Range("C4")
             '   Only look at single cell changes
            If Target.Count > 1 Then Exit Sub
            '   Only look at that range
            If Intersect(Target, rng) Is Nothing Then Exit Sub
            '   Action if Condition(s) are met (do your thing here...)
            Rows("5:23").Hidden = Not Rows("5:23").Hidden
End Sub

But the problem with this was every time i changed C4, regardless of what it was changed to, the rows would hide then unhide. I only want to see the rows when C4 displays as Y and not see the rows when C4 is blank or N. I'll need to use this all along the sheet for C23, C40, C56, C68, C90, C110, C122, C131, C139, C151 with the rows in between those locations hidden/unhidden based on their selection.

All help is very much appreciated!
 

Some videos you may like

Excel Facts

What do {} around a formula in the formula bar mean?
{Formula} means the formula was entered using Ctrl+Shift+Enter signifying an old-style array formula.

Fluff

MrExcel MVP, Moderator
Joined
Jun 12, 2014
Messages
51,128
Office Version
  1. 365
Platform
  1. Windows
Hi & welcome to MrExcel.
How about
VBA Code:
Private Sub Worksheet_Change(ByVal Target As Range)
   If Target.CountLarge > 1 Then Exit Sub
   If Target.Column <> 3 Then Exit Sub
   Select Case Target.Address(0, 0)
      Case "C4"
         Rows("5:22").Hidden = Not Target.Value = "Y"
      Case "C23"
         Rows("24:39").Hidden = Not Target.Value = "Y"
   End Select
End Sub
 
Solution

JQB87

New Member
Joined
Oct 19, 2020
Messages
6
Office Version
  1. 365
Platform
  1. Windows
Private Sub Worksheet_Change(ByVal Target As Range) If Target.CountLarge > 1 Then Exit Sub If Target.Column <> 3 Then Exit Sub Select Case Target.Address(0, 0) Case "C4" Rows("5:22").Hidden = Not Target.Value = "Y" Case "C23" Rows("24:39").Hidden = Not Target.Value = "Y" End Select End Sub
That is amazing! I think that's it, thank you very much. How do I save this without losing the code?
 

Fluff

MrExcel MVP, Moderator
Joined
Jun 12, 2014
Messages
51,128
Office Version
  1. 365
Platform
  1. Windows
You will need to save the file as macro enabled, XLSM file.
 

JQB87

New Member
Joined
Oct 19, 2020
Messages
6
Office Version
  1. 365
Platform
  1. Windows
You will need to save the file as macro enabled, XLSM file.
Awesome thank you so very much! I've been trying to figure that out for a couple days now
 

Fluff

MrExcel MVP, Moderator
Joined
Jun 12, 2014
Messages
51,128
Office Version
  1. 365
Platform
  1. Windows
You're welcome & thanks for the feedback.
 

Watch MrExcel Video

Forum statistics

Threads
1,119,221
Messages
5,576,821
Members
412,748
Latest member
MikeyP14
Top