May 21 2024 11:07 AM
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!
May 21 2024 11:32 AM
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
Jun 06 2024 01:34 PM
@HansVogelaar thank you so much for your help with this!