How to Hide/Unhide multiple sets of rows in VBA based on cell value

[LauraBlair]

Hi all,

I have created a file that has 7 different templates (Different template to enter for a different cell value) on the one sheet, in cell D1 the cell value will change, and based on that value it will hide / unhide rows to show the template that needs to be filled in. I am quite new to VBA / macros and when i run the attached code, only the last one runs properly, the others dont hide/show the correct rows.

Please help. Thankyou

 

[JLGWhiz]

Please copy and paste your code into the reply pane as text (not screen shot) then usiing the mouse pointer, select the code (just like prep to copy) and click the VBA icon in the tool bar of the reply pane. Then click 'Post Reply' or 'Save' as applicable.

 

[LauraBlair]

Please copy and paste your code into the reply pane as text (not screen shot) then usiing the mouse pointer, select the code (just like prep to copy) and click the VBA icon in the tool bar of the reply pane. Then click 'Post Reply' or 'Save' as applicable.

ActiveSheet.Activate
If Not Application.Intersect(Range("D1"), Range(Target.Address)) Is Nothing Then
    Select Case Target.Value
    Case Is = "WS127": Rows("43:1000").EntireRow.Hidden = True
                Rows("43:1000").EntireRow.Hidden = False
    Case Is = "WS891": Range("3:43,80:1000").EntireRow.Hidden = False
                Range("3:43,80:1000").EntireRow.Hidden = True
    Case Is = "WS202": Range("3:80,122:1000").EntireRow.Hidden = False
                Range("3:80,122:1000").EntireRow.Hidden = True
    Case Is = "WS69": Range("3:80,122:1000").EntireRow.Hidden = False
                Range("3:80,122:1000").EntireRow.Hidden = True
    End Select

End If

End Sub

 

[LauraBlair]

So

Please copy and paste your code into the reply pane as text (not screen shot) then usiing the mouse pointer, select the code (just like prep to copy) and click the VBA icon in the tool bar of the reply pane. Then click 'Post Reply' or 'Save' as applicable.

Sorry, first time posting and wasnt sure how to do that. Thank you

 

[JLGWhiz]

Maybe if you explained what you expect it to do, we could fix it. See my comments in the code below

ActiveSheet.Activate
If Not Application.Intersect(Range("D1"), Range(Target.Address)) Is Nothing Then
    Select Case Target.Value
    Case Is = "WS127": Rows("43:1000").EntireRow.Hidden = True
                Rows("43:1000").EntireRow.Hidden = False 'This reverses the the entire range of rows that were just hidden if Case is True
    Case Is = "WS891": Range("3:43,80:1000").EntireRow.Hidden = False
                Range("3:43,80:1000").EntireRow.Hidden = True  'This reverses the entire range of rows that were just unhidden if Case is True
    Case Is = "WS202": Range("3:80,122:1000").EntireRow.Hidden = False
                Range("3:80,122:1000").EntireRow.Hidden = True  'This reverses the entire range of rows that were unhidden if Case is True
    Case Is = "WS69": Range("3:80,122:1000").EntireRow.Hidden = False
                Range("3:80,122:1000").EntireRow.Hidden = True  'This reverses the entire range of rows that were unhidden if Case is True
    End Select
End If
End Sub

The select case statement is odd because it first either hides or unhides a range and then immediately reverses that action. but unless this Select Case is in a loop ( and it does not appear to be) then only one or none of the statments will execute when the macro is run. The Select Case statement executes the first 'True' statement and then exits the Select Case.

 

[Michael M]

Are you trying to toggle the Hide / Unhide of the rows

If Not Application.Intersect(Range("D1"), Range(Target.Address)) Is Nothing Then
    Select Case Target.Value
    Case Is = "WS127": Rows("43:1000").EntireRow.Hidden = Not Rows("43:1000").EntireRow.Hidden 
    Case Is = "WS891": Range("3:43,80:1000").EntireRow.Hidden = Not Range("3:43,80:1000").EntireRow.Hidden 
    Case Is = "WS202": Range("3:80,122:1000").EntireRow.Hidden = Not Range("3:80,122:1000").EntireRow.Hidden = True 
    Case Is = "WS69": Range("3:80,122:1000").EntireRow.Hidden = Not Range("3:80,122:1000").EntireRow.Hidden = True  
    End Select

As @JLGWhiz said only one statement will function. I'm guessing 4 IF stements might be better

If Not Application.Intersect(Range("D1"), Range(Target.Address)) Is Nothing Then
    If Target.Value = "WS127" Then Rows("43:1000").EntireRow.Hidden = Not Rows("43:1000").EntireRow.Hidden 
    If Target.Value = "WS891" Then Range("3:43,80:1000").EntireRow.Hidden = Not Range("3:43,80:1000").EntireRow.Hidden 
    If Target.Value = "WS202" Then Range("3:80,122:1000").EntireRow.Hidden = Not Range("3:80,122:1000").EntireRow.Hidden 
    If Target.Value = "WS69" Then Range("3:80,122:1000").EntireRow.Hidden = Not Range("3:80,122:1000").EntireRow.Hidden

 

[LauraBlair]

Maybe if you explained what you expect it to do, we could fix it. See my comments in the code below

ActiveSheet.Activate
If Not Application.Intersect(Range("D1"), Range(Target.Address)) Is Nothing Then
    Select Case Target.Value
    Case Is = "WS127": Rows("43:1000").EntireRow.Hidden = True
                Rows("43:1000").EntireRow.Hidden = False 'This reverses the the entire range of rows that were just hidden if Case is True
    Case Is = "WS891": Range("3:43,80:1000").EntireRow.Hidden = False
                Range("3:43,80:1000").EntireRow.Hidden = True  'This reverses the entire range of rows that were just unhidden if Case is True
    Case Is = "WS202": Range("3:80,122:1000").EntireRow.Hidden = False
                Range("3:80,122:1000").EntireRow.Hidden = True  'This reverses the entire range of rows that were unhidden if Case is True
    Case Is = "WS69": Range("3:80,122:1000").EntireRow.Hidden = False
                Range("3:80,122:1000").EntireRow.Hidden = True  'This reverses the entire range of rows that were unhidden if Case is True
    End Select
End If
End Sub

The select case statement is odd because it first either hides or unhides a range and then immediately reverses that action. but unless this Select Case is in a loop ( and it does not appear to be) then only one or none of the statments will execute when the macro is run. The Select Case statement executes the first 'True' statement and then exits the Select Case.

Thank you.

I think i have completely stuffed up the code, sorry im a bit new to using VBA.

The back story of this file, there is currently 7 different templates all on separate tabs in a file, and an analyst has to determine which template they need to fill in (out of the 7 templates) based on Álert number such as (WS127) in code above. There are over 150 alert numbers but all fall into 1 of the 7 templates (Once i have the VBA code working, i will do all the remaining alerts numbers in the VBA code, currently i only have 4).

What i have done is put all 7 templates in the 1 sheet one after the other going down the rows, in cell D1 i have all the alert numbers in a drop down list which the analyst can select. Based on the selection in cell D1, this cell then determines which of the 7 templates should be shown (hiding/unhiding) particular rows.

There have been many errors with analysts choosing the wrong template to fill out, so this is to eliminate error, all the analyst needs to do is enter the alert number and then the template that needs to be filled out is shown, all other templates will be hidden.

Thanks so much for your help.

 

[LauraBlair]

Are you trying to toggle the Hide / Unhide of the rows

If Not Application.Intersect(Range("D1"), Range(Target.Address)) Is Nothing Then
    Select Case Target.Value
    Case Is = "WS127": Rows("43:1000").EntireRow.Hidden = Not Rows("43:1000").EntireRow.Hidden
    Case Is = "WS891": Range("3:43,80:1000").EntireRow.Hidden = Not Range("3:43,80:1000").EntireRow.Hidden
    Case Is = "WS202": Range("3:80,122:1000").EntireRow.Hidden = Not Range("3:80,122:1000").EntireRow.Hidden = True
    Case Is = "WS69": Range("3:80,122:1000").EntireRow.Hidden = Not Range("3:80,122:1000").EntireRow.Hidden = True 
    End Select

As @JLGWhiz said only one statement will function. I'm guessing 4 IF stements might be better

If Not Application.Intersect(Range("D1"), Range(Target.Address)) Is Nothing Then
    If Target.Value = "WS127" Then Rows("43:1000").EntireRow.Hidden = Not Rows("43:1000").EntireRow.Hidden
    If Target.Value = "WS891" Then Range("3:43,80:1000").EntireRow.Hidden = Not Range("3:43,80:1000").EntireRow.Hidden
    If Target.Value = "WS202" Then Range("3:80,122:1000").EntireRow.Hidden = Not Range("3:80,122:1000").EntireRow.Hidden
    If Target.Value = "WS69" Then Range("3:80,122:1000").EntireRow.Hidden = Not Range("3:80,122:1000").EntireRow.Hidden

Thank you.

I have tried the above, but only 1 statement still only works using an IF statement.

The back story of this file, there is currently 7 different templates all on separate tabs in a file, and an analyst has to determine which template they need to fill in (out of the 7 templates) based on Álert number such as (WS127) in code above. There are over 150 alert numbers but all fall into 1 of the 7 templates (Once i have the VBA code working, i will do all the remaining alerts numbers in the VBA code, currently i only have 4).

What i have done is put all 7 templates in the 1 sheet one after the other going down the rows, in cell D1 i have all the alert numbers in a drop down list which the analyst can select. Based on the selection in cell D1, this cell then determines which of the 7 templates should be shown (hiding/unhiding) particular rows.

There have been many errors with analysts choosing the wrong template to fill out, so this is to eliminate error, all the analyst needs to do is enter the alert number and then the template that needs to be filled out is shown, all other templates will be hidden. Well this is what i was trying to get the VBA code to do, but cant get it to work, it only runs 1 statement.

Thanks so much for your help.

 

[JLGWhiz]

Well this is what i was trying to get the VBA code to do, but cant get it to work, it only runs 1 statement.

If you are testing the value in cell D1, then only one of the choices in the Select Case statement will execute. Same with an 'If...ElseIf...End If' algorithim. As soon as the code hits a True statement, it executes that statement and then exits the algorithim. It does not loop to see if another statement is true. You would need to change the value in cell D1 and then run the macro again to get a difrferent result. Or you could write the code to include a loop that changes the value in D1, which would then test more than one value in a single run of the macro.
But it will not execute more than one of the options in the Select Case statement as currently written. What do you want the code to do?