Hiding rows using a drop down menu using vba

[Paulrh78]

Hi,

I would like to hide specific rows based on a drop down menu so I can create a dashboard that has various elements on it for a series of locations, I have tried many sites and used much code without any success. The code needs to be in the worksheet so that no macro button is required.

Regards
Paul

 

[Nikunj Rastogi]

Please share raw data with drop down so that we can give you a solution.

 

[Paulrh78]

The formatting has been corrupted when I downloaded to my Mac (no surprise), anyway there is a slicer at the top of sheet linked to a pivot in cell W4, as far as I am aware you can not link a macro to this cell and have to use a dropdown list as in cell U4; anyway what i am trying to do is from rows 27 to 116 select the rows that apply to the slicer when selected, for example if I press Angmering it changes all my cells in rows 11 to 24 and I would like to also only show the rows from 27 to 116 that are applicable to Angmering and apply the same logic to all locations in my Slicer.

Regards
Paul

 

[Paulrh78]

Hi,

Think I have worked it out for myself; thanks for your help.

Regards

Paul

Private Sub Worksheet_Change(ByVal Target As Range)
    If Range("S8").Value = "Angmering" Then
        Rows("26:116").EntireRow.Hidden = False
        Rows("29:116").EntireRow.Hidden = True
    ElseIf Range("S8").Value = "Bexhill-on-Sea" Then
        Rows("26:116").EntireRow.Hidden = False
        Rows("27:28").EntireRow.Hidden = True
        Rows("31:116").EntireRow.Hidden = True

 

[Nikunj Rastogi]

Great