Hiding/unhiding rows based upon the values in multiple cells Hi, I\'ve worked wi

Question

Hiding/unhiding rows based upon the values in multiple cells

Hi, I've worked with code to hide/unhide rows based upon the value in a single cell, but that's the limit of my expertise. I therefore got some help from this community with the code below (which works perfectly), but am in a bit of trouble now as I need to modify it and can't figure out how.

As you can see below, this is a worksheet change event based upon changes to cell J3 that will hide/unhide certain rows. What I need to do is break each possible outcome into 2 as follows:

- Case "Department"

If J4 = "Sales Volume", then unhide rows 10:52, 355:361, 364

If J4 = "# Of Sales", then unhide rows 10:52, 355:359, 362:364

- Case "Type"

If J4 = "Sales Volume", then unhide rows 55:60, 365:371, 374

If J4 = "# Of Sales", then unhide rows 55:60, 365:369, 372:374

- Case "Salesperson"

Each of the 3 possible outcomes below opens 333:348 (in addition to other rows). As respects rows 333:348 only (I still want all of the other rows to hide/unhide as they are currently), I want that group of rows to operate as follows for all 3 possible outcomes:

If J4 = "Sales Volume", then unhide rows 333:339, 342:345, 348

If J4 = "# Of Sales", then unhide rows 333:337, 340:343, 346:348

Thank you!

Private Sub Worksheet_Change(ByVal Target As Range)
   Const primeCellAddress = "$J$3"
   Const altCellAddress1 = "$J$1"
   Const altCellAddress2 = "$J$2"
     
   If Target.Address <> primeCellAddress Then
     Exit Sub
   End If
   On Error GoTo RecoverEventProcessing
   Application.EnableEvents = False
   Application.ScreenUpdating = False
   ActiveSheet.Unprotect
   Rows("10:394").EntireRow.Hidden = True
   Select Case Target
     Case "Department"
       Rows("10:52").EntireRow.Hidden = False
       Rows("355:364").EntireRow.Hidden = False
     Case "Type"
       Rows("55:60").EntireRow.Hidden = False
       Rows("365:374").EntireRow.Hidden = False
     Case "Salesperson"
       If Range(altCellAddress1) = "All Units" Then
         Rows("152:255").EntireRow.Hidden = False
       Rows("333:348").EntireRow.Hidden = False
       Else
         If Range(altCellAddress2) = "All Segments" Then
       Rows("152:153").EntireRow.Hidden = False
           Rows("258:309").EntireRow.Hidden = False
       Rows("333:348").EntireRow.Hidden = False
         Else
       Rows("152:153").EntireRow.Hidden = False
           Rows("312:348").EntireRow.Hidden = False
         End If
       End If
   End Select
RecoverEventProcessing:
   If Err <> 0 Then
     Err.Clear
   End If
   On Error GoTo 0
   ActiveSheet.Protect
   Application.EnableEvents = True
End Sub

Thanks !!

Explanation / Answer

Hi, I didn't catch your previous thread, but, I think I have understood your request.

With a little manipulation and by using the Range object instead of Rows, the code can be simplified... and answer your request. Using range you can enter multiple row references, but note that a single row in the range statement is represented by row:row still...

For Case statements it is possible to put them onto a single row using : to separate the statements (some people like, some don't). See if this does what you are expecting...

Private Sub Worksheet_Change(ByVal Target As Range)
Const altCellAddress1 = "$J$1"
Const altCellAddress2 = "$J$2"
Const primeCellAddress = "$J$3"
Const secondCellAddress = "$J$4"
On Error GoTo RecoverEventProcessing
If Target.Address <> primeCellAddress Then Exit Sub
Application.EnableEvents = False
Application.ScreenUpdating = False
ActiveSheet.Unprotect
Rows("10:394").EntireRow.Hidden = True
Select Case Target.Value & Range(secondCellAddress).Value
Case "DepartmentSales Volume": Range("10:52,355:361,364:364").EntireRow.Hidden = False
Case "Department# Of Sales": Range("10:52,355:359,362:364").EntireRow.Hidden = False
Case "TypeSales Volume": Range("55:60,365:371,374:374").EntireRow.Hidden = False
Case "Type# Of Sales": Range("55:60,365:369,372:374").EntireRow.Hidden = False
Case "SalespersonSales Volume": Range("333:339,342:345,348:348").EntireRow.Hidden = False
Case "Salesperson# Of Sales": Range("333:337,340:343,346:348").EntireRow.Hidden = False
End Select
If Target.Value = "Salesperson" Then
If Range(altCellAddress1) = "All Units" Then
Range("152:255").EntireRow.Hidden = False.
ElseIf Range(altCellAddress2) = "All Segments" Then
Range("152:153,258:309").EntireRow.Hidden = False
Else
Range("152:153,312:332").EntireRow.Hidden = False
End If.
End If
RecoverEventProcessing:
If Err <> 0 Then Err.Clear
On Error GoTo 0.
ActiveSheet.Protect.
Application.EnableEvents = True
Application.ScreenUpdating = True
End Sub

Related Questions

Navigate

• Browse (All)
• Browse H
• Subjects

Integrity-first tutoring: explanations and feedback only — we do not complete graded work. Learn more.