Automatically hide/unhide rows based on cell value in excel

[jag2011]

Hi,
I'm new to excel VBA.
Need to do the following in a worksheet "Inlet chevron"
If cell A3 = "Combination", then hide rows 7 to 64 and unhide rows 65 to 94
If cell A3 ="Single", then hide rows 65 to 94 and unhide rows 7 to 64
Appreciate if you could type it down so that I could copy it.
Thanks in Advance.

 

[VoG]

Try this: right click the sheet tab, select View Code and paste in

Private Sub Worksheet_Change(ByVal Target As Range)
If Target.Address(False, False) = "A3" Then
    Select Case Target.Value
        Case "Combination": Rows("7:64").Hidden = True: Rows("65:94").Hidden = False
        Case "Single": Rows("7:64").Hidden = False: Rows("65:94").Hidden = True
    End Select
End If
End Sub

then try changing the value of A3.

 

[jag2011]

Forgot to mention working on excel 2003.
Copied and pasted, but not working.

 

[VoG]

It is working here in Excel 2000.

Macros must be enabled (lowest security setting).

What changes A3 - you or does it contain a formula?

 

[Weaver]

Could be a case issue

The following works if the value in A3 is either one or the other

Option Compare Text
Private Sub Worksheet_Change(ByVal Target As Range)
    If Target.Address = "$A$3" Then
        Rows("7:64").Hidden = (Target.Value = "Combination")
        Rows("65:94").Hidden = (Target.Value = "Single")
    End If
End Sub

The 'option compare' statement cancels out case sensitivity. Make sure it appears at the top of any module you use it in.

 

[jag2011]

Thanks a lot.
The latest one sent is working.

 

[Lo_cm]

Hi there,

I'm having a similar problem. I am able to hide a range of rows based the value of a certain cell but I'm having trouble hiding specific rows. If Sheet 1 B47 is blank or n/a then I need a list of rows in sheet 2 to be hidden ie row 10, 25, 40, 88 and 103.

I've tried this but it gives me an error (my sheet 2 is named Evaluation)

Option Compare Text
Private Sub Worksheet_Change(ByVal Target As Range)
If Target.Address = "$B$47" Then
Sheets("Evaluation").Rows("10:10,25:25,40:40,88:88,103:103").EntireRow.Hidden = (Target.Value = "N/A")
End If
End Sub

Also I need the rows to un-hide as soon as the cell B47 is populated with info

Please help!

 

[heapy]

Hi could someone help me please on first post....

I have below code which i copies from this post but it doesnt work exactly how i need.
I want to hide rows based on drop down box in cell A10
if i have "5 Metre" selected, only rows 22-23 are hidden. I beleive this is because 6 Metre and 7 Metre are false so rows 24-40 wont hide. I tried & metre at the top but still wont work.
Anyone able to help with my problem?
Thanks Heapy


Private Sub Worksheet_Change(ByVal Target As Range)
If Target.Address = "$A$10" Then
Rows("22:40").Hidden = (Target.Value = "5 Metre")
Rows("24:40").Hidden = (Target.Value = "6 Metre")
Rows("26:40").Hidden = (Target.Value = "7 Metre")

 

[mpShock]

I am trying to do this but it seems to not work due to a formula that i have in "K3". how to i get the value that is in K3 (which is the formula =VLOOKUP(TRIM(C3),'OFF Seniority'!A3:AK2501,5,FALSE) )? from what i can tell i need to evaluate it some how.

thank you for your help in advance


Private Sub Worksheet_Change(ByVal Target As Range)

If Target.Address(False, False) = "K3" Then

Select Case Target.Value
Case "W1": Rows("35:47").Hidden = True: Rows("69:74").Hidden = True
Case "W2": Rows("35:47").Hidden = True: Rows("69:74").Hidden = True
Case "W3": Rows("35:47").Hidden = True: Rows("69:74").Hidden = True
Case "W4": Rows("35:47").Hidden = True: Rows("69:74").Hidden = True
Case "W5": Rows("35:47").Hidden = True: Rows("69:74").Hidden = True
Case "O1": Rows("35:47").Hidden = False: Rows("69:74").Hidden = False
Case "O2": Rows("35:47").Hidden = False: Rows("69:74").Hidden = False
Case "O3": Rows("35:47").Hidden = False: Rows("69:74").Hidden = False
Case "O4": Rows("35:47").Hidden = False: Rows("69:74").Hidden = False
Case "O5": Rows("35:47").Hidden = False: Rows("69:74").Hidden = False
Case "O6": Rows("35:47").Hidden = False: Rows("69:74").Hidden = False
Case "O7": Rows("35:47").Hidden = False: Rows("69:74").Hidden = False
Case "O8": Rows("35:47").Hidden = False: Rows("69:74").Hidden = False
End Select

End If
End Sub

 

[jhawklocke]

I'm also having a problem here. I have two cells that I want to compare ("I19" and "120"). If cell "i19" is larger than "i20" then i want rows 26-389 in the worksheet to be hidden. I also want this macro to run continuously as the value in i19 will change dialy. Here is what i have set up but I don't understand what I need to do to accomplish this:

Private Sub Worksheet_Change(ByVal Target As Range) Dim LOCtrigger, currentLOC, hiddenrange, permdebt As Range
Set LOCtrigger = Worksheets("asset #1").Range("i20")
Set currentLOC = Worksheets("asset #1").Range("i19")
Set hiddenrange = Worksheets("asset #1").Rows("26:389")

If currentLOC <= LOCtrigger Then
hiddenrange.Hidden = True
Else
hiddenrange.Hidden = False
End If

End Sub