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

Jason44136

New Member
Joined
Jul 8, 2019
Messages
18
Office Version
  1. 365
Platform
  1. Windows
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!
 

Excel Facts

Enter current date or time
Ctrl+: enters current time. Ctrl+; enters current date. Use Ctrl+: Ctrl+; Enter for current date & time.
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
 
Upvote 0

Forum statistics

Threads
1,213,536
Messages
6,114,211
Members
448,554
Latest member
Gleisner2

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