Hide Rows Based on Drop Down List Selection

BRYCEPIETROWIAK

New Member
Joined
May 12, 2021
Messages
6
Office Version
  1. 365
Platform
  1. Windows
Hello - I am trying to find the best way to update my VBA code to show the number of content item tables based on the number you select in the drop down in cell D3. For example, if you select 3, I would like the New Content Upload Request, Content Item #1, Content Item #2 & Content Item #3 tables to appear and hide Content Item #4 & Content Item #5 tables.

Here is a link to my file.
 

Excel Facts

How to show all formulas in Excel?
Press Ctrl+` to show all formulas. Press it again to toggle back to numbers. The grave accent is often under the tilde on US keyboards.
Here is my current code.
VBA Code:
Private Sub Worksheet_Change(ByVal Target As Range)

Dim PayType As Range
Set PayType = Range("D3")

If Intersect(Target, PayType) Is Nothing Then Exit Sub
'add as many data sets as required
Dim Rng1 As Range
Dim Rng2 As Range
Dim Rng3 As Range
Dim Rng4 As Range
Dim Rng5 As Range

'add as many options as you require
Dim FindHdg1 As Range
Dim FindHdg2 As Range
Dim FindHdg3 As Range
Dim FindHdg4 As Range
Dim FindHdg5 As Range

'put your headings in the brackets & add more headings if required
Set FindHdg1 = Cells.Find("CONTENT ITEM #1")
Set FindHdg2 = Cells.Find("CONTENT ITEM #2")
Set FindHdg3 = Cells.Find("CONTENT ITEM #3")
Set FindHdg4 = Cells.Find("CONTENT ITEM #4")
Set FindHdg5 = Cells.Find("CONTENT ITEM #5")

Dim RowsToHide As Range
Set RowsToHide = Range("A16:A1048576")


'add a case for each option in your drop-down & and add more if required
    Select Case PayType
        Case Is = "ALL - DO NOT SELECT"
            Cells.EntireRow.Hidden = False
       
        Case Is = "1"
            Cells.EntireRow.Hidden = False
            Set Rng1 = FindHdg1.CurrentRegion
            RowsToHide.EntireRow.Hidden = True
            Rng1.EntireRow.Hidden = False
       
        Case Is = "2"
            Cells.EntireRow.Hidden = False
            Set Rng2 = FindHdg2.CurrentRegion
            RowsToHide.EntireRow.Hidden = True
            Rng2.EntireRow.Hidden = False
       
        Case Is = "3"
            Cells.EntireRow.Hidden = False
            Set Rng3 = FindHdg3.CurrentRegion
            RowsToHide.EntireRow.Hidden = True
            Rng3.EntireRow.Hidden = False
                   
        Case Is = "4"
            Cells.EntireRow.Hidden = False
            Set Rng3 = FindHdg3.CurrentRegion
            RowsToHide.EntireRow.Hidden = True
            Rng3.EntireRow.Hidden = False
           
        Case Is = "5"
            Cells.EntireRow.Hidden = False
            Set Rng3 = FindHdg3.CurrentRegion
            RowsToHide.EntireRow.Hidden = True
            Rng3.EntireRow.Hidden = False
    End Select
   
End Sub
 
Upvote 0

Forum statistics

Threads
1,213,537
Messages
6,114,216
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