Help! Hiding rows in sheet2 based on values in sheet1

[Oti]

Hello everyone. I'm new to vba and am looking for some guidance. I am using excel2003. Here's my problem...
sheet1 cells a1 to a16 contain a list of parts to be built. sheet1 cells b1 to b16 each contain a drop down list limiting values to "yes" or "no". "no" in sheet1 B1 needs to hide rows 16 and 17 on sheet2. "no" in sheet1 B2 needs to hide rows 18-20 on sheet2., "no" in sheet1 B3 needs to hide sheet2 rows 21-29, etc. What is the proper way to do this? A "yes" value i sheet1 should also make sure that the rows are visible. Is this even possible?

 

[Andrew Poulsom]

Welcome to MrExcel.

Example for the first 3 cells:

Sub Test()
    With Worksheets("Sheet1")
        Worksheets("Sheet2").Rows("16:17").Hidden = .Range("B1").Value = "no"
        Worksheets("Sheet2").Rows("18:20").Hidden = .Range("B2").Value = "no"
        Worksheets("Sheet2").Rows("21:29").Hidden = .Range("B3").Value = "no"
    End With
End Sub

 

[Oti]

Thanks Andrew! This works! Next question...is there a way for this to update automatically when switching from sheet1 to sheet2?

 

[merlin777]

i cant do vba so i tend to look for non-vba solutions.

if you point a cell from each row you want to hide on sheet at the trigger cells on sheet 1, you can then apply a filter on that column to hide what you don't want to see. I think you can hide this coloumn and the row with the filter to keep things tidy. I just tried a simple test an it seemed to work.

 

[Andrew Poulsom]

Put this in the module for Sheet2:

Private Sub Worksheet_Activate()
    With Worksheets("Sheet1")
        Me.Rows("16:17").Hidden = .Range("B1").Value = "no"
        Me.Rows("18:20").Hidden = .Range("B2").Value = "no"
        Me.Rows("21:29").Hidden = .Range("B3").Value = "no"
    End With
End Sub

 

[Oti]

Thank you Andrew! This works flawlessly! Your help is greatly appreciated.