hide and unhide rows based the selections in my dropdown list. Please help

[CAspect]

I am hoping someone can help me. I am creating a form in excel using the active x combo drop down option. I am trying to hide rows based on the option they pick.

I added the active combo box in cell B3 with options A, B, and C.

Based on what they pick I need the following to happen...

If they pick option A I want to see only rows 6 - 16, but hide 19-64
If they pick option B I want to see only rows 19 - 40, but hide 6-16 and 42-64
If they pick option C I want to see only rows 42-64, but hide 6-16 and 19-40

Can anyone help me with how to write the macro for this? I have tried for days now. Please help.

 

[JustynaMK]

Hi CAspect,

In your Excel, go to Developer ribbon > Design Mode, right-click on your Combo Box and click on "View Code". This will take you to VBA window where you can paste the following code:

Private Sub ComboBox1_Change()
    With ActiveSheet
    .Rows("6:64").Hidden = False
        Select Case ComboBox1.Value
            Case "Option A"
                .Rows("19:64").Hidden = True
            Case "Option B"
                .Rows("6:16").Hidden = True
                .Rows("42:64").Hidden = True
            Case "Option C"
                .Rows("6:16").Hidden = True
                .Rows("19:40").Hidden = True
        End Select
    End With
End Sub

Two comments:
1) You might need to rename all 3 cases, as I assumed your 3 possible choices are "Option A", "Option B" and "Option C".
2) I suspect you will also need to modify hidden ranges as I did not quite understand your description. Taking Option A as an example, if you are hiding rows 19-64, then my macro will hide exactly these rows (19-64). But you also described that you only want to see rows 6-16 (what happens to rows 17, 18?). If you have any problems with modifying this part please let me know.

Good luck,
Justyna

 

[CAspect]

Hi Justyna,
Thank you so much!!! It worked. I can't thank you enough for your help. Appreciate it!

 

[JustynaMK]

No problem at all take care.