Show/Hide Certain Rows Based on Picklist Selection

Copper Contributor

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!

2 Replies

@axelromr295 

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

@Hans Vogelaar thank you so much for your help with this!