Hide and unhide rows based on referenced cell

I'm using this vba code to hide rows and B3 is reference cell resulted from vlookupvalue which brings values from another sheet. The code is not working. File attached for your review.


Private Sub Worksheet_Change(ByVal Target As Range)
If Target.Address(False, False) = "B3" Then
Select Case Target.Value
Case "1": Rows("11:13").Hidden = True: Rows("9:10").Hidden = False
Case "2": Rows("9:12").Hidden = True: Rows("13").Hidden = False
Case "3": Rows("10:12").Hidden = True: Rows("9").Hidden = False
Case "4": Rows("11:12").Hidden = True: Rows("10").Hidden = False: Rows("9").Hidden = True
Case "5": Rows("9:11").Hidden = True: Rows("12").Hidden = False: Rows("13").Hidden = True
Case "6": Rows("11").Hidden = True: Rows("12").Hidden = False: Rows("13").Hidden = True: Rows("10").Hidden = True
Case "7": Rows("11").Hidden = True: Rows("12").Hidden = False: Rows("13").Hidden = True: Rows("9").Hidden = True
End Select
End If
End Sub

Hi,

I'm just wondering if I'm looking at the wrong thing, but the rows are hiding without issue for me...

The only thing I can think of is that perhaps you have macros disabled...?

Let me know

I didn't try to change cell F4, sorry. After looking at your code, the code indicates that I need to change cell B3 in order to hide/unhide any rows. Perhaps this is what is going wrong - try changing the value of B3 and see if you get the result that you are after.

Let me know how you go with this, and if you still can't figure it out I'll take a look at the spreadsheet next time I'm on my computer.

ajryan88; Yes. The code says B3. But B3 is related to e2 and e2 has a vlookup looking into f4. The idea hide/unhiden will happen seamless without me pressing enter. It will change based on the lookup in cell E2. If you have a chance check the spreadsheet.


Joseph;
i updated the code using Calculate. It stops working. Would you please review the code and let me know what i'm doing wrong. Thank you for your assistance

Private Sub Worksheet_Calculate(ByVal Target As Excel.Range)
If Target.Address(False, False) = "B3" Then
Select Case Target.Value
Case "1": Rows("11:13").Hidden = True: Rows("9:10").Hidden = False
Case "2": Rows("9:12").Hidden = True: Rows("13").Hidden = False
Case "3": Rows("10:12").Hidden = True: Rows("9").Hidden = False
Case "4": Rows("11:12").Hidden = True: Rows("10").Hidden = False: Rows("9").Hidden = True
Case "5": Rows("9:11").Hidden = True: Rows("12").Hidden = False: Rows("13").Hidden = True
Case "6": Rows("11").Hidden = True: Rows("12").Hidden = False: Rows("13").Hidden = True: Rows("10").Hidden = True
Case "7": Rows("11").Hidden = True: Rows("12").Hidden = False: Rows("13").Hidden = True: Rows("9").Hidden = True
End Select
End If
End Sub

The value that will change is E2. E2 is a vlookup(E1,E:F,2). I need to monitor E2 as user will select different values in E1

Hi Joseph, Do you mind taking a look of the attachment.This file provide a better understanding of what i'm trying to accomplish.

Thank you again for all your help...

Try this.

The macro now looks for a change in F4 and references the value in B3 when that change occurs. It seems to be working fine now when you change F4.

Let me know

hi ajryan88,
Would you please take a look of this file. Your code works well in the sample file but not in the file where i have the data. Do you mind?
Thank you so much for your time and patience. I'm new to vba...

No problem. I will let you know how I go a bit later today

This works fine for me again, the only thing I can comment on is perhaps that you haven't defined whether row 12 should be hidden or unhidden in the case that cell B3 is changed to "3".

If fixing this doesn't resolve your issue, could you please describe exactly what error(s) you are encountering (i.e. when you change what cell to what value, what do you expect to see and what actually happens instead - answer each of these points for each and every error that you come across).

Hope this resolves your issue.

Btw, when you are happy with the solution, please do not forget to mark the thread as solved and also click the * to the bottom-left of my post to say thanks

Ajryan88,

Thank you for all your help. It works.

You're very welcome.

Btw, when you are happy with the solution, please do not forget to mark the thread as solved and also click the * to the bottom-left of my post to say thanks

Thank you Joseph