Hi, I have a worksheet change event that hides a pre-defined set of rows for eac

Question

Hi, I have a worksheet change event that hides a pre-defined set of rows for each option in a dropdown (simplified version of the code below). The trouble is that the rows to hide keeps changing so it has become onerous to administer the code. I am therefore hoping to get some help with revising the code below so that:

- If the dropdown selection is "Tom", then any row that has an "x" in column CQ will be hidden.

- If the dropdown selection is "Jack", then any row that has an "x" in column CR will be hidden.

Please let me know if any questions and thank you very much for any help you can provide.

Private Sub Worksheet_Change(ByVal Target As Excel.Range)

    If Not Intersect(Range("D9"), Target) Is Nothing Then

        Application.EnableEvents = False

    Application.ScreenUpdating = False

        Me.Unprotect

        Rows("15:442").EntireRow.Hidden = False

        Select Case Range("D9").Value

            Case "Tom"

                Rows("15:17").EntireRow.Hidden = True

                Rows("19:21").EntireRow.Hidden = True

                Rows("436:437").EntireRow.Hidden = True

                Rows("440:441").EntireRow.Hidden = True

            Case "Jack"

                Rows("15:17").EntireRow.Hidden = True

                Rows("19:50").EntireRow.Hidden = True

                Rows("433:437").EntireRow.Hidden = True

                Rows("440:441").EntireRow.Hidden = True

            Case "All"

                Rows("15:442").EntireRow.Hidden = False

    Rows("15:442").Select

    Selection.Rows.AutoFit

       End Select

    Range("B12").Select

        Me.Protect

     Application.ScreenUpdating = True

        Application.EnableEvents = True

    End If

End Sub

Explanation / Answer

Any code which inserts data into the sheet of interest will fire the Worksheet_Change event code. To avoid this inconvenience, you should enclose the relevant other code in structure of the kind

On Error GoTo ErrHandler

Application.EnableEvents = False

'\ your code

....

....

ErrHandler:

Application.EnableEvents = True

If you need assistance with such adaptation, post the relevant code.

===

Regards,

Related Questions

Navigate

• Browse (All)
• Browse H
• Subjects

Integrity-first tutoring: explanations and feedback only — we do not complete graded work. Learn more.