Hide/Unhide variety of ranges of rows based on cell value in dropdown list (Dynamic)

Jason44136

New Member
Joined
Jul 8, 2019
Messages
8
Greetings. I have a spreadsheet to calculate incentive pay based on title. The title is pulled from a data validation list in cell F7. There are 10 possible titles and when selected, i want a variety of ranges of rows to be hidden. The hidden rows may be different for multiple titles. There are 121 rows total. Rows 1-29 are always visible.
For example, if RVP of F&B is selected, rows 49-114 need hidden but all others should be visible. However, if Corp Director is selected in F7, the following ranges need hidden: 30:62, 68:72, 81:88, & 94:121 thus showing all other rows.

I need this to automatically update when a different title is selected in F7. Thank you!
 

My Aswer Is This

Well-known Member
Joined
Jul 5, 2014
Messages
16,459
Try this:

This is an auto sheet event script
Your Workbook must be Macro enabled
To install this code:
Right-click on the sheet tab
Select View Code from the pop-up context menu
Paste the code in the VBA edit window

When you manually change the value in Range("F7") the script will run.
Add more select cases as needed

Code:
Private Sub Worksheet_Change(ByVal Target As Range)
'Modified  8/1/2019  12:59:09 AM  EDT
If Target.Cells.CountLarge > 1 Or IsEmpty(Target) Then Exit Sub
If Target.Address = Range("F7").Address Then
With ActiveSheet
Select Case Target.Value
Case "RVP"
    .Rows.Hidden = False
    Rows("49:114").Hidden = True
Case "Corp Director"
    .Rows.Hidden = False
    Rows("30:62").Hidden = True
    Rows("68:72").Hidden = True
    Rows("81:88").Hidden = True
    Rows("94:121").Hidden = True
    
'add more here as needed
End Select
End With
End If
End Sub
 

Forum statistics

Threads
1,085,853
Messages
5,386,353
Members
401,996
Latest member
mg07p929

Some videos you may like

This Week's Hot Topics

Top