Forum Discussion

LaMamaDePetra's avatar
LaMamaDePetra
Copper Contributor
May 24, 2022

Hide and Unhide Rows Based on Cell Value

Hello everyone hope you are doing good.  Im in need of help with a form im doing on excel, i need a code to make rows hide and unhide based on cell value from a data validation list. Ive found some ...
  • HansVogelaar's avatar
    May 24, 2022

    LaMamaDePetra

    The code belongs in the worksheet module; you can activate this by right-clicking the sheet tab and selecting 'View Code' from the context menu.

    VBA requires straight quotes " around text strings, not curly quotes “.

    Try this version of the code. Change "secret" to the password that you used to protect the sheet (use "" if none).

    Private Sub Worksheet_Change(ByVal Target As Range)
        If Not Intersect(Range("J5"), Target) Is Nothing Then
            Application.EnableEvents = False
            Me.Unprotect Password:="secret"
            Range("A6:A61").EntireRow.Hidden = True
            Select Case Range("J5").Value
                Case "US$ USD"
                    Range("A6:A33").EntireRow.Hidden = False
                Case "RD$ DOP"
                    Range("A34:A61").EntireRow.Hidden = False
            End Select
            Me.Protect Password:="secret"
            Application.EnableEvents = True
        End If
    End Sub

Resources