How to make multiple, non adjacent cells hidden based on cell value code

I have a spread sheet that I want to hide multiple rows, some of them are not adjacent, and I am basically new to coding in excel. I want several rows to hide based on the value of B2, and when B2 changes, I want some of those rows to show, and others to hide, all variable on the value of B2. I have been banging my head on the wall trying to figure this out, even though I know it is probably basic coding.

I want the following rows hidden for each value in B2:

ACSA - 24, 25, 35, 36, 57, 58, 60, all other rows not hidden
CSA - 57, 58, 60, all other rows not hidden
ASA - 24, 25, 30, 31, 35, 36, 50, 57, 58, 59, 60, all other rows not hidden
ACA - 24, 25, 30, 31, 35, 36, 50, 59, 60, all other rows not hidden
SASA - 24, 25, 30, 31, 35, 36, 50, 57, 58, 59, 60, all other rows not hidden
Please Select Role Code - all rows not hidden

This is what I have so far:

Code:

Private Sub Worksheet_Change(ByVal Target As Range) ActiveSheet.Activate If Not Application.Intersect(Range("B2"), Range(Target.Address)) Is Nothing Then Select Case Target.Value Case Is = "ACSA": Rows("35:36").EntireRow.Hidden = True Case Is = "CSA": Rows("35:36").EntireRow.Hidden = False Case Is = "ASA": Rows("35:36").EntireRow.Hidden = True Case Is = "ACA": Rows("35:36").EntireRow.Hidden = True Case Is = "SASA": Rows("35:36").EntireRow.Hidden = False Case Is = "Please Select Role Code": Rows("35:36").EntireRow.Hidden = False End Select End If If Not Application.Intersect(Range("D38"), Range(Target.Address)) Is Nothing Then Select Case Target.Value Case Is = "Select Answer": Rows("39:47").EntireRow.Hidden = False Case Is = "Yes": Rows("39:47").EntireRow.Hidden = True Case Is = "No": Rows("39:47").EntireRow.Hidden = False Case Is = "NA": Rows("39:47").EntireRow.Hidden = True End Select End If End Sub

I would really appreciate it! I am so lost at this point :banghead::banghead:

1 Attachment(s)

1. Welcome to the Forum - suggest you take a look at the FAQs (in my sig)

2. I added CODE tags to format the macro for you - you can use the [#] icon to insert CODE tags and paste the macro between

3. No sure about the interplay between the two cells but something like this maybe on the code sheet for the worksheet

Code:

Option Explicit Private Sub Worksheet_Change(ByVal Target As Range) Dim v As Variant Dim r As Range Dim Rows39_47 As Boolean Set r = Target.Cells(1, 1) If Len(r.Value) = 0 Then Exit Sub ' catch a Clear If Not Application.Intersect(Range("B2"), Target) Is Nothing Then Application.ScreenUpdating = False Rows39_47 = Me.Rows("39:47").Hidden Me.Rows.Hidden = False Me.Rows("39:47").Hidden = Rows39_47 Select Case UCase(r.Value) Case Is = "ACSA" For Each v In Array(24, 25, 35, 36, 57, 58, 60) Me.Rows(v).Hidden = True Next Case Is = "CSA" For Each v In Array(57, 58, 60) Me.Rows(v).Hidden = True Next Case Is = "ASA" For Each v In Array(24, 25, 30, 31, 35, 36, 50, 57, 58, 59, 60) Me.Rows(v).Hidden = True Next Case Is = "ACA" For Each v In Array(24, 25, 30, 31, 35, 36, 50, 59, 60) Me.Rows(v).Hidden = True Next Case Is = "SASA" For Each v In Array(24, 25, 30, 31, 35, 36, 50, 57, 58, 59, 60) Me.Rows(v).Hidden = True Next Case Is = "PLEASE SELECT ROLE CODE" ' empty for completeness End Select Application.ScreenUpdating = True ElseIf Not Application.Intersect(Range("D38"), Target) Is Nothing Then Application.ScreenUpdating = False Select Case UCase(r.Value) Case Is = "SELECT ANSWER" Me.Rows("39:47").Hidden = False Case Is = "YES" Me.Rows("39:47").Hidden = True Case Is = "NO" Me.Rows("39:47").Hidden = False Case Is = "NA" Me.Rows("39:47").Hidden = True End Select Application.ScreenUpdating = True End If End Sub

This is Pauls's Code. I refactored it to reflect my own preference for NOT overloading an Event sub.

Code:

Option Explicit Private Sub Worksheet_Change(ByVal Target As Range) If Len(Target.Value) = 0 Then Exit Sub ' catch a Clear If Target.Address = "$B$2" Then HideB2 Target If Target.Address = "$D$38" Then HideD38 Target End Sub

Code:

Private Sub HideB2(Target As Range) Dim v, ACSA, CSA, ASA, ACA, SASA ACSA = Array(24, 25, 35, 36, 57, 58, 60) CSA = Array(57, 58, 60) ASA = Array(24, 25, 30, 31, 35, 36, 50, 57, 58, 59, 60) ACA = Array(24, 25, 30, 31, 35, 36, 50, 59, 60) SASA = ASA Rows.Hidden = False Select Case UCase(Target.Value) Case Is = "ACSA" For Each v In ACSA Me.Rows(v).Hidden = True Next Case Is = "CSA" For Each v In CSA Me.Rows(v).Hidden = True Next Case Is = "ASA" For Each v In ASA Me.Rows(v).Hidden = True Next Case Is = "ACA" For Each v In ACA Me.Rows(v).Hidden = True Next Case Is = "SASA" For Each v In SASA Me.Rows(v).Hidden = True Next Case Is = "PLEASE SELECT ROLE CODE" ' empty for completeness Case Else End Select Application.ScreenUpdating = True HideD38 Range("D38") End Sub

Code:

Private Sub HideD38(Target As Range) Application.ScreenUpdating = False Select Case UCase(Target.Value) Case Is = "SELECT ANSWER" Me.Rows("39:47").Hidden = False Case Is = "YES" Me.Rows("39:47").Hidden = True Case Is = "NO" Me.Rows("39:47").Hidden = False Case Is = "NA" Me.Rows("39:47").Hidden = True End Select Application.ScreenUpdating = True End Sub

SamT --

1. Nothing wrong with the refactoring

2. Be advised that after you unhide all Rows

Code:

Rows.Hidden = False

Code:

If Target.Address = "$D$38" Then HideD38 Target

you might have to hide someD38 rows, even if D38 wasn't the Target

That was why I have to remember the D38 rows Hidden status, unhide all Rows, hide/unhide the D38 rows, and then hide the appropriate B2 rows

Quote:

you might have to hide someD38 rows, even if D38 wasn't the Target

Code:

Rows.Hidden = False ' 'Handle B2 Rows ' HideD38 Range("D38")

Sam - 1

Paul - 0

Hi All,

Thanks for your help the following code is working for me that Paul published, it works great, the only thing i need added is I just need to add the following, if:

D12 is value Yes - Rows 12-17 are not hidden
D12 value is No - Rows 12-17 are hidden
D12 value is NA - Rows 12-17 are hidden
D12 value is Select Answer - Rows 12-17 are not hidden

I was trying to copy the information for the coding of rows 88-47 and add this, but I am not doing it right. Thanks for being patient with me. This code I got from Paul works great, I just need to incorporate the 12-17 row info above, and I will be all done! Again thanks so much!!

Option Explicit

Code:

Private Sub Worksheet_Change(ByVal Target As Range) Dim v As Variant Dim r As Range Dim Rows39_47 As Boolean Set r = Target.Cells(1, 1) If Len(r.Value) = 0 Then Exit Sub ' catch a Clear If Not Application.Intersect(Range("B2"), Target) Is Nothing Then Application.ScreenUpdating = False Rows39_47 = Me.Rows("39:47").Hidden Me.Rows.Hidden = False Me.Rows("39:47").Hidden = Rows39_47 Select Case UCase(r.Value) Case Is = "ACSA" For Each v In Array(24, 25, 35, 36, 57, 58, 60) Me.Rows(v).Hidden = True Next Case Is = "CSA" For Each v In Array(57, 58, 60) Me.Rows(v).Hidden = True Next Case Is = "ASA" For Each v In Array(24, 25, 30, 31, 35, 36, 50, 57, 58, 59, 60) Me.Rows(v).Hidden = True Next Case Is = "ACA" For Each v In Array(24, 25, 30, 31, 35, 36, 50, 59, 60) Me.Rows(v).Hidden = True Next Case Is = "SASA" For Each v In Array(24, 25, 30, 31, 35, 36, 50, 57, 58, 59, 60) Me.Rows(v).Hidden = True Next Case Is = "PLEASE SELECT ROLE CODE" ' empty for completeness End Select Application.ScreenUpdating = True ElseIf Not Application.Intersect(Range("D38"), Target) Is Nothing Then Application.ScreenUpdating = False Select Case UCase(r.Value) Case Is = "SELECT ANSWER" Me.Rows("39:47").Hidden = False Case Is = "YES" Me.Rows("39:47").Hidden = True Case Is = "NO" Me.Rows("39:47").Hidden = False Case Is = "NA" Me.Rows("39:47").Hidden = True End Select Application.ScreenUpdating = True End If End Sub

I just added a ElseIf at the end and another Rows12_17 Boolean to track status

However ...

Quote:

D12 is value Yes - Rows 12-17 are not hidden
D12 value is No - Rows 12-17 are hidden
D12 value is NA - Rows 12-17 are hidden
D12 value is Select Answer - Rows 12-17 are not hidden

I was trying to copy the information for the coding of rows 88-47 and add this, but I am not doing it right. Thanks for being patient with me. This code I got from Paul works great, I just need to incorporate the 12-17 row info above, and I will be all done! Again thanks so much!!

... if Row 12 is Hidden because of D12 = "No", how would you put in "Yes"?

Code:

Option Explicit Private Sub Worksheet_Change(ByVal Target As Range) Dim v As Variant Dim r As Range Dim Rows39_47 As Boolean, Rows12_17 As Boolean Set r = Target.Cells(1, 1) If Len(r.Value) = 0 Then Exit Sub ' catch a Clear If Not Application.Intersect(Range("B2"), Target) Is Nothing Then Application.ScreenUpdating = False Rows39_47 = Me.Rows("39:47").Hidden Rows12_17 = Me.Rows("12:17").Hidden Me.Rows.Hidden = False Me.Rows("39:47").Hidden = Rows39_47 Me.Rows("12:17").Hidden = Rows12_17 Select Case UCase(r.Value) Case Is = "ACSA" For Each v In Array(24, 25, 35, 36, 57, 58, 60) Me.Rows(v).Hidden = True Next Case Is = "CSA" For Each v In Array(57, 58, 60) Me.Rows(v).Hidden = True Next Case Is = "ASA", "SASA" For Each v In Array(24, 25, 30, 31, 35, 36, 50, 57, 58, 59, 60) Me.Rows(v).Hidden = True Next Case Is = "ACA" For Each v In Array(24, 25, 30, 31, 35, 36, 50, 59, 60) Me.Rows(v).Hidden = True Next Case Is = "PLEASE SELECT ROLE CODE" ' empty for completeness End Select Application.ScreenUpdating = True ElseIf Not Application.Intersect(Range("D38"), Target) Is Nothing Then Application.ScreenUpdating = False Select Case UCase(r.Value) Case Is = "SELECT ANSWER", "NO" Me.Rows("39:47").Hidden = False Case Is = "YES", "NA" Me.Rows("39:47").Hidden = True Case Else Me.Rows("39:47").Hidden = False End Select Application.ScreenUpdating = True ElseIf Not Application.Intersect(Range("D12"), Target) Is Nothing Then Application.ScreenUpdating = False Select Case UCase(r.Value) Case Is = "NO", "NA" Me.Rows("12:17").Hidden = True Case Is = "YES", "SELECT ANSWER" Me.Rows("12:17").Hidden = False Case Else Me.Rows("12:17").Hidden = False End Select Application.ScreenUpdating = True End If End Sub

I polished this a bit: combined Case's that had same result, and added a Case Else just in Case (pun intended)