CODEWITHSUNDEEP
excelvba
Nicole Spiele
Nicole Spiele

Reputation: 13

Hide rows based on choice

I have a bunch of choices that can be made in the cells (C41:C59) using a dropdown, the value of the dropdown also is seen in the cells (E41:E59). For each choice I want different rows to show or hide. Hide if the dropdown is N/A else show the rows. The problem i can't get around is that every choice has different rows and als a different amount of rows. So I tried to make a code per choice and only change this when the cell in column E changes. This is what I have so far, but doesn't do anything.

If Not Application.Intersect(Target, Range("E41")) Is Nothing Then
        If Range("E41") = "N/A" Then
            [67:73].EntireRow.Hidden = True
        Else
            [67:73].EntireRow.Hidden = False
        End If
    End If

Upvotes: 1

Views: 72

Answers (1)

Variatus
Variatus

Reputation: 14373

The code below is an event procedure. It runs when a cell is changed on the worksheet in whose code sheet the procedure is found. (The location of the code in that particular module is paramount.) If a single cell was changed - ignoring multiple simultaneous changes such as might be caused by copy/paste action - the code will check if the modified cell was in the ranges C41:C59 or E41:E59. If so, it will hide or show rows in the same worksheet depending upon whether or the cell's value is "N/A" after modification.

Private Sub Worksheet_Change(ByVal Target As Range)
    ' 010

    Dim TriggerRange As Range
    Dim Rng As Range

    ' ignore simultaneous changes of many cells
    If Target.Cells.CountLarge > 1 Then Exit Sub

    Set TriggerRange = Application.Union(Range("C41:C59"), Range("E41:E59"))
    If Not Application.Intersect(TriggerRange, Target) Is Nothing Then
        Select Case Target.Row
            Case 41, 46, 59
                Set Rng = Range("67:73")
            Case 50 To 59
                Set Rng = Range(Rows(67), Rows(73))
            Case Else
                Set Rng = Range(Rows(67), Rows(73))
        End Select
        Rng.Rows.Hidden = (Target.Value = "N/A")
    End If
End Sub

In this code always the same rows are hidden or shown. The code serves to demonstrate how you could specify different row ranges depending upon which row the changed cell is in, using different syntax depending upon your preference.

Upvotes: 1

Related Questions