Hide Rows based on dropdown selection...

Greetings, Gurus.

I have a worksheet on which the user would have to make 2 different selections from a dropdown listbox. Box 1's cell link is M2, and the user can select from 3 cases, (empty, "New" or 'Recycled"). Box 2 is linked to cell S2, and the user can select from 3 cases, (empty, "US" or "Canada").

I would like to automatically hide certain rows based on the selections. If the boxes are empty, (showing "1" in the associated linked cell), then I want all rows visible. If "New" is selected in Box 1, (the value in M2 would be "2"), I want to hide rows 8, 10, 15 and 17. If "Recycled" is selected, (the value in M2 would be "3"), I want to hide rows 14, 16, 20 and 22.

Ideally, these rows would hide automatically when the selections are made, without having to push another button. I don't know if the code would be entered into a regular module, or the sheet itself.

Any suggestions would be much appreciated.

Hutch

Hello Hutch,

Are you using a Validation drop down or a Forms tool bar Drop Down?

Sincerely,
Leith Ross

Hello Hutch,

Sorry about the delay. The phone always seems to ring whenever I am coding. Copy this code to a Standard module and then attach the macro to your first drop down. This code doesn't look at the second drop down's selection to unhide the rows. That can be changed easy enough.

Sub ShowHideRows()

  Dim Cbo As DropDown
  Dim Rng As Range
  
    Set Cbo = ActiveSheet.DropDowns(Application.Caller)
    
      With Cbo
        Select Case .ListIndex
          Case 1
            With ActiveSheet
              Set Rng = .Rows(8)
              Set Rng = Union(Rng, .Rows(10), .Rows(15), .Rows(17), _
                        .Rows(14), .Rows(16), .Rows(20), .Rows(22))
            End With
            Rng.Rows.Hidden = False
          Case 2
            With ActiveSheet
              Set Rng = .Rows(8)
              Set Rng = Union(Rng, .Rows(10), .Rows(15), .Rows(17))
            End With
            Rng.Rows.Hidden = True
          Case 3
             With ActiveSheet
              Set Rng = .Rows(14)
              Set Rng = Union(Rng, .Rows(16), .Rows(20), .Rows(22))
            End With
            Rng.Rows.Hidden = True
        End Select
      End With
      
End Sub

Sincerely,
Leith Ross

That works beautifully, Leith. I think I can figure it out from here, but if not, I'll be back in touch.

Thanks again!

Hutch