Automatically hide/unhide rows based on cell value in excel

jag2011

New Member
Joined
Jun 28, 2011
Messages
6
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.:)
 

Excel Facts

Using Function Arguments with nested formulas
If writing INDEX in Func. Arguments, type MATCH(. Use the mouse to click inside MATCH in the formula bar. Dialog switches to MATCH.
Try this: right click the sheet tab, select View Code and paste in

Code:
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.
 
Upvote 0
It is working here in Excel 2000.

Macros must be enabled (lowest security setting).

What changes A3 - you or does it contain a formula?
 
Upvote 0
Could be a case issue

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

Code:
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.
 
Upvote 0
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! :)
 
Last edited:
Upvote 0
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")
 
Upvote 0
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
 
Upvote 0
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
 
Upvote 0

Forum statistics

Threads
1,213,531
Messages
6,114,167
Members
448,554
Latest member
Gleisner2

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Back
Top