Hide\Show rows based upon a changeable value in a cell

[Stev3i_D]

Hi all,

Newbie and not at all experienced in VB. I've done a search but to be honest, I'm not sure what I should be looking for. So here goes.

I have a workbook with several sheets. In sheet5 I have created a list which allows me to select different equipment types. When I use the list it places a value in N8; either 1,2 or 3.

1 = 220, 2 = 650 & 3 = 36

I need a macro that will look at the value in N8 and then hide rows, in sheet5, based upon the value.

1, hide rows 17:22 + 52:91. 2, hide rows 17:52. 3, hide rows 23:91.

in essence, each time I select the equipment type from the list, it should only display the options for that piece of equipment.

Thanks in advance..

Excel 2010 Win 7

 

[trunten]

Right click on the sheet tab for sheet 5 and select view code. then past the following into the code pane:

Private Sub Worksheet_Change(ByVal Target As Range)
    If Not Application.Intersect(Target, Range("N8")) Is Nothing Then
        Application.ScreenUpdating = False
        Application.EnableEvents = False
        Rows("17:91").Hidden = False
        Select Case Target.Value
            Case 220
                Rows("17:22").Hidden = True
                Rows("52:91").Hidden = True
            Case 650
                Rows("17:52").Hidden = True
            Case 36
                Rows("23:91").Hidden = True
        End Select
        Application.ScreenUpdating = True
        Application.EnableEvents = True
    End If
End Sub

 

[Stev3i_D]

Hi trunten,

Thanks for the speedy response. It works, brilliant, in so far as it hides the appropriate rows based on the value in N8. However, it will only do this if I enter the appropriate value directly in to cell N8, when in fact N8 is updated by my selecting a piece of equipment from the list in F2.

I was wondering, is it possible to do this or do I have to manually enter the value in to N8?

 

[Stev3i_D]

trunten, actually... I've been re-working the sheet around your method; it makes more sense to do away with the List, replacing it with direct input.

Thanks for the script and of course your time.

Steve