How to hide rows based on dropdownlist?-CodePudding

I got to know to hide basic rows like as screenshot: When I select 1 it hides rows 9-14 and so on.

Code:

Private Sub Worksheet_Change(ByVal Target As Range)
Dim TrigerCell As Range

Set Triggercell = Range("D2")

If Not Application.Intersect(Triggercell, Target) Is Nothing Then
        If Triggercell.Value = 1 Then
            Rows("9:14").Hidden = False
            Rows("11:14").Hidden = True
        ElseIf Triggercell.Value = 2 Then
            Rows("9:14").Hidden = False
            Rows("13:14").Hidden = True
        ElseIf Triggercell.Value = 3 Then
            Rows("9:14").Hidden = False
        End If
End If
End Sub

But when I have many column with same name. How can I hide below screenshot`s rows: Since there is not specific cell like above D2. I want to hide from 27-36 when I select the option 1,2 and unhide the same rows when I select option3.

CodePudding user response：

No need of any variable (besides Target). Please, use the next adapted code:

Option Explicit

Private Sub Worksheet_Change(ByVal Target As Range)

 If Target.address = "$D$2" Then
        If Target.Value = 1 Then
            Me.rows("9:14").Hidden = False
            Me.rows("11:14").Hidden = True
        ElseIf Target.Value = 2 Then
            Me.rows("9:14").Hidden = False
            Me.rows("13:14").Hidden = True
        ElseIf Target.Value = 3 Then
            Me.rows("9:14").Hidden = False
        End If
 End If
End Sub

CodePudding user response：

Your sheet has column widths set to 1, and then columns are joined to create fields of certain width. E.g. the three first columns ('A', 'B' and 'C') for each of the rows 10..17 are combined to provide the 'NO' field.

Combined cells use the reference of the leftmost cell, iow, the combined cell with the content 'NO' has the cell reference 'A10', the combined cell beneath it (with text '1') has the cell reference 'A11'.

After you have decided which cell to use as trigger cell, select it and look at the reference box for its cell notation (A11 in example below). Use that reference in your hide and show logic.