Hide and unhide rows based on a dropdown list selection

[tsshchi]

Hi everyone

I am new to VBA and struggling with creating coding to hide and unhide rows based the selections in my dropdown list.

I went through a lot of tutorials and couldn't find a suitable one to do exactly what I want.

I have a dropdown list in cell D1 with three options namely 1,2 and 3. Also, I have some data from row 9-14.

With option 1 : I want to hide row number 11 to 14 showing only 9-10
With option 2 : I want to hide row number 13 to 14 showing only 9-12
With option 3 : I want to hide nothing showing all 9-14

Please help as I have been struggling for a few days already.....

 

[Zubair Muhammad]

Re: How to hide and unhide rows based on a dropdown list selection

Hi tsshchi

Enter the code below in the worksheet module.
Hopefully it will do your job

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

Set Triggercell = Range("D1")
   
    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

 

[tsshchi]

Re: How to hide and unhide rows based on a dropdown list selection

Zubair Muhammad

You just saved my day I really really really appreciate your answer. This is exactly what I needed!!!!!

 

[tsshchi]

But I face one more question....I have some codes in front which is shown below
Private Sub Worksheet_Change(ByVal Target As Range)
On Error Resume Next
If Target.Column = 2 Then
If Target.Validation.Type = 3 Then
Application.EnableEvents = False
Target.Offset(0, 1).ClearContents
End If
End If
exitHandler:
Application.EnableEvents = True
Exit Sub
End Sub

When I cope and paste you coding in and run it. Then there is a pop up warning message shown:

Compile error:
Ambiguous name detected : Worksheet_change

What should I do??????

 

[My Aswer Is This]

Edit

 

[tsshchi]

It seems like my problem has been fixed. Thank you so much!!!!!!

 

[Adie]

Hello is there anyway I can use worksheet 1 (trigger cell D2=1) as the worksheet 2 follow VBA code from worksheet 1. Sorry for my bad english

 

[SDSSask]

Re: How to hide and unhide rows based on a dropdown list selection

Hi tsshchi

Enter the code below in the worksheet module.
Hopefully it will do your job

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

Set Triggercell = Range("D1")
  
    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

Hi, how would you adapt this code to work with 4 separate triggers cells. I'm using Case statements rather an If / ElseIf and need all the un hidden rows to display for each triggercell selected. Thanks.