Nov 19 2021 07:04 AM
Hi,
I have a spreadsheet I am trying to create where I have a range of 7 different values in a drop down list. What I to happen is when I select a certain entry from the drop down list that a number of different rows on the spreadsheet will be hidden depending on the entry selected from the drop down. Trying to get it to do this automatically when the drop down selection is changed. I know this is to be done through VBA but a bit of help as to how to start getting this rolling would be greatly appreciated.
Many Thanks
Nov 19 2021 07:13 AM
Let's say the dropdown is in B1, and its values are Text1, Text2, ..., Text7.
Right-click the sheet tab.
Select 'View Code' from the context menu.
Copy the following code into the worksheet module:
Private Sub Worksheet_Change(ByVal Target As Range)
' Has the cell with the dropdown changed?
If Not Intersect(Range("B1"), Target) Is Nothing Then
Application.ScreenUpdating = False
Application.EnableEvents = False
' Unhide all rows in the relevant range
Range("A4:A59").EntireRow.Hidden = False
' Inspect the value of the dropdown
Select Case Range("B2").Value
Case "Text1"
Range("A4:A11").EntireRow.Hidden = True
Case "Text2"
Range("A12:A19").EntireRow.Hidden = True
Case "Text3"
Range("A20:A27").EntireRow.Hidden = True
Case "Text4"
Range("A28:A35").EntireRow.Hidden = True
Case "Text5"
Range("A36:A43").EntireRow.Hidden = True
Case "Text6"
Range("A44:A51").EntireRow.Hidden = True
Case "Text7"
Range("A52:A59").EntireRow.Hidden = True
End Select
Application.EnableEvents = True
Application.ScreenUpdating = True
End If
End Sub
Change the code as needed.
Switch back to Excel.
Save the workbook as a macro-enabled workbook (.xlsm)
Make sure that you allow macros when you open it.
Nov 22 2021 01:23 AM
@Hans Vogelaar Thanks muchly for that Hans. It has worked a treat.