VBA Help to UNHIDE rows in a sheet based on a cell value

[hchahal]

Hi, I am really new to this and need help in unhiding some rows on my sheet based on the cell value of another cell which has a drop down validation list. I have already added in code for the same sheet that works for another section where I want this, but cant get the next one to work. So in my example, if cell C41 has the drop down value "012 - Alcoholic Beverages", then i want rows "43:45" to become unhidden (currently manually hidden), if it is any other value or blank I need it to stay hidden. Code already pasted in is:

Private Sub Worksheet_Change(ByVal Target As Range)
If Range("B3").Value = "Food" Then
Range("A41:A44").EntireRow.Hidden = True
Else
Range("A41:A44").EntireRow.Hidden = False
End If
End Sub

Your simple resolution to this problem would be greatly appreciated!
Thanks

[leelnich]

Hi hchahal, welcome to the forum! To make you code more readable, we ask that you select it, then click # on the Editing Toolbar. This wraps it in code tags, so it looks like the code below. Thanks.

So, is this what you had in mind? Or do you want to loop through several check values, each with different associated rows?

Please Login or Register  to view this content.

[hchahal]

Hi Leelnich...thansks for the fast reply....This is not working for me. Not sure if I made it clear but the code I am already using I want that to stay as is for other rows to become unhidden when someone selects for for cell B3...when I try and add a further statement in using the same details but amendsing the cells and what should be false or true i get the run time error 40036.....any ideas? Thanks

[leelnich]

Your approach should have worked. Could you post the code that produced the error, or better yet, attach a sample version of your workbook? NOTE: you may need to reverse True and False in the original code (mine AND yours) to get the desired result.

To attach a workbook:
Click Edit Post (or just start a new reply.)
Click Go Advanced
Scroll down to Manage Attachments and click.
Now just Browse for your file, then click Upload. Simple!

[hchahal]

Hi...see below for what I have, had to change the cells for the second part as I needed to add in more rows:
Private Sub Worksheet_Change(ByVal Target As Range)
If Range("B3").Value = "Food" Then
Range("A40:A44").EntireRow.Hidden = True
Else
Range("A40:A44").EntireRow.Hidden = False
End If
End Sub
Private Sub Worksheet_Change(ByVal Target As Range)
If Range("C54").Value = "012 - Alcoholic Beverages" Then
Range("A56:A58").EntireRow.Hidden = False
Else
Range("A56:A58").EntireRow.Hidden = True
End If
End Sub

[leelnich]

Please Login or Register  to view this content.

You've defined TWO Worksheet_Change event procedures. Each worksheet only gets ONE. Remove the highlighted lines and do both checks within the same procedure.
NOTE: If you have several checks to do, there are more efficient approaches to this task.

[hchahal]

leelnich...works great now...I knew I was doing something simple wrong...typical, getting too old!. Many Thanks for your help and swift replies.

[leelnich]

Happy to help! If your question is answered, please go to Thread Tools up top and mark your thread as SOLVED. Thanks-Lee

Please click the Add Reputation star below any helpful posts, and if you have your answer, mark your thread as SOLVED (Thread Tools up top). Thanks!-Lee