Hide Rows based on dropdown list in another sheet

**Jeeperman4x4** · 09-16-2009, 09:24 PM

Hi how do I hide rows from a sheet based on a drop down list in another sheet in the same workbook?

I have 2 spreadsheets:

"MENU" with the dropdown list "Project Status Report" and "Project Closure Report" and
"3. Report" in which the hidden rows will be: Rows 132 to 185 if "Project Status Report" is selected and Rows 102 to 130 if "Project Closure Report is selected"

Thanks!
Regards

**Leith Ross** · 09-16-2009, 10:25 PM

Hello jeeperman4x4,

Welcome to the Forum!

You will need to add a ComboBox control to the worksheet from the Control ToolBox tool bar. Once you have done that, add this code to the control. Set the ListFillRange property to the worksheet and range that will be used for the selections.

Private Sub ComboBox1_Click()

  Select Case ComboBox1.ListIndex
    Case 0
      With Worksheets("Project Status Report")
        .Range(.Rows(132), .Rows(185)).Hidden = True
      End With
    Case 1
      With Worksheets("Project Status Report")
        .Range(.Rows(102), .Rows(130)).Hidden = True
      End With
  End Select
  
End Sub

**Jeeperman4x4** · 09-19-2009, 08:50 AM

Thanks Leith it Works!!!

**royUK** · 09-19-2009, 09:59 AM

If you are satisfied with the solution(s) provided, please mark your thread as Solved.

How to mark a thread Solved
Go to the first post
Click edit
Click Go Advanced
Just below the word Title you will see a dropdown with the word No prefix.
Change to Solved
Click Save

**Justinmih** · 05-04-2011, 07:26 PM

Hi There,

I am trying to adapt this Code to suit a program I am working on however it is saying I need to add a ComboBox to the workbook first.

How do i do this with Excel for MAC 2011?

Cheers

Justin

Originally Posted by Leith Ross

Hello jeeperman4x4,

Welcome to the Forum!

You will need to add a ComboBox control to the worksheet from the Control ToolBox tool bar. Once you have done that, add this code to the control. Set the ListFillRange property to the worksheet and range that will be used for the selections.

Private Sub ComboBox1_Click()

  Select Case ComboBox1.ListIndex
    Case 0
      With Worksheets("Project Status Report")
        .Range(.Rows(132), .Rows(185)).Hidden = True
      End With
    Case 1
      With Worksheets("Project Status Report")
        .Range(.Rows(102), .Rows(130)).Hidden = True
      End With
  End Select
  
End Sub

**nimrod** · 05-04-2011, 08:01 PM

Hello Justinmih:
Do you realize this feature is already built into excel ? A "Custom View" drop down can be dragged onto your tool bar that does this.