Forum Discussion
axelromr295
May 21, 2024Copper Contributor
Show/Hide Certain Rows Based on Picklist Selection
Hi all! I’m looking for help creating a code to show/hide specific rows when certain options are selected. I looked at some previous responses but couldn’t figure out how to adapt it for my needs.
In my spreadsheet, there’s a multiple choice drop down picklist in cell B12 with picklist options titled Claim 1-8. I’m looking to show/hide rows 16-23, which correspond to the claim 1-8 numbering in the B12 drop down. For example- if claims 1 and 6 are selected in cell B12, I want to show Row 16 and 21, and hide all others.
Would love any help or advice!
I assume that you already have a Worksheet_Change event on the worksheet module to handle the multiple selection. Change it to
Private Sub Worksheet_Change(ByVal Target As Range) Dim oldVal As String Dim newVal As String Dim i As Long If Not Intersect(Range("B12"), Target) Is Nothing Then Application.ScreenUpdating = False Application.EnableEvents = False newVal = Range("B12").Value If newVal <> "" Then Application.Undo oldVal = Range("B12").Value If oldVal = "" Then Range("B12").Value = newVal ElseIf InStr(oldVal, newVal) Then oldVal = Replace(oldVal & ", ", newVal & ", ", "") If Left(oldVal, 2) = ", " Then oldVal = Mid(oldVal, 3) ElseIf Right(oldVal, 2) = ", " Then oldVal = Left(oldVal, Len(oldVal) - 2) End If Range("B12").Value = oldVal Else Range("B12").Value = oldVal & ", " & newVal End If End If For i = 1 To 8 Range("A" & i + 15).EntireRow.Hidden = (InStr(Range("B12").Value, "Claim " & i) = 0) Next i Application.EnableEvents = True Application.ScreenUpdating = True End If End Sub
- axelromr295Copper Contributor
HansVogelaar thank you so much for your help with this!