Forum Discussion

axelromr295's avatar
axelromr295
Copper Contributor
May 21, 2024

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!

  • 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

Resources