# unhide rows based on value of particular cell?

#### ajm

##### Well-known Member
how can i unhide a range of cells dependent upon the value of a particular cell above? so, if cell C16 is between 11 and 20, unhide rows 50-60, if between 21 and 30, unhide 50 - 70, etc etc

### Excel Facts

Test for Multiple Conditions in IF?
Use AND(test, test, test, test) or OR(test, test, test, ...) as the logical_test argument of IF.
You need a bit of code to do this. The following assumes that:
1. You want to selectively show rows between 51 and 100
2. C16 is the trigger cell
Code:
``````Private Sub Worksheet_Change(ByVal Target As Range)
If Target.Cells.Count > 1 Then Exit Sub
If Intersect(Target, Range("C16")) Is Nothing Then Exit Sub
Rows("51:100").RowHeight = 0
Select Case Target.Value
Case 11 To 20
Rows("51:60").AutoFit
Case 21 To 30
Rows("61:70").AutoFit
Case 31 To 40
Rows("71:80").AutoFit
Case 41 To 50
Rows("81:90").AutoFit
Case 51 To 60
Rows("91:100").AutoFit
End Select
End Sub``````
Note: I moved the opening cells down in groups of 10, for testing. Adjust to suit.

The code first hides all conditional rows, then displays the desired ones.

Denis

denis,

how can i loop it so if the number in the target cell changes, the hidden cells change accordingly?

should i just shorten each section to a piece of code itself? ie

Private Sub Worksheet_Change(ByVal Target As Range)
If Target.Cells.Count > 1 Then Exit Sub
If Intersect(Target, Range("C19")) Is Nothing Then Exit Sub
Rows("48:222").RowHeight = 0
Select Case Target.Value
Case 11 To 20
Rows("48:72").AutoFit
End Select
End Sub

Private Sub1 Worksheet_Change(ByVal Target As Range)
If Target.Cells.Count > 1 Then Exit Sub
If Intersect(Target, Range("C19")) Is Nothing Then Exit Sub
Rows("48:222").RowHeight = 0
Select Case Target.Value
Case 21 To 30
Rows("73:97").AutoFit
End Select
End Sub

etc etc etc???

ajm

The place where you hsow / hide, according to different values, is in the Select Case statement.

I created cutoffs 21 To 30, 31 To 40, 41 To 50 etc. Each of these is a Case statement.
The line directly below each of these shows which rows to unhide.
All you need to do to extend it is to add more Case statements.

If you want to vary WHICH rows appear, change Rows("61:70").Autofit (for example) to Rows("51:70").Autofit

The code goes into the module for the worksheet. Select the sheet tab, right-click, View Code, and paste the code in there. Every time you change C16, the code will run.

Denis

ok. made a mistake in not specifying that i needed to unhide more than JUST the particular segment. its cumulative so if the target cell is greater than 10, i want to see rows 1 - 20, if its greater than 20, i want to see rows 1 -30. as you suggest, just clean up therow selection.

many thanks Denis.

PS could this have been done with an If Else If...

Denis,

forgot to ask, if Target C19 is linked to another cell in the workbook or has a formula in it instead of just a number, how can the code that you gave me be modified to accommodate this?

1. Yes it could have been done iwth a nestsed If ... ElseIf but it gets messy. The Select Case statement is much easier to maintain if the number of conditions gets large, or will vary over time.

2. If C19 is linked to another cell (say, F19) then make the changing cell the target. The Worksheet_Change event doesn't fire on a cell that changes as the result of a link or calculation. This version of the code changes rows in Sheet2, based on a trigger value in Sheet1:
Code:
``````Private Sub Worksheet_Change(ByVal Target As Range)
Dim Sht As Worksheet
If Target.Cells.Count > 1 Then Exit Sub
If Intersect(Target, Range("C16")) Is Nothing Then Exit Sub
Set Sht = Sheets("Sheet2")

With Sht
.Rows("51:100").RowHeight = 0
Select Case Target.Value
Case 11 To 20
.Rows("51:60").AutoFit
Case 21 To 30
.Rows("61:70").AutoFit
Case 31 To 40
.Rows("71:80").AutoFit
Case 41 To 50
.Rows("81:90").AutoFit
Case 51 To 60
.Rows("91:100").AutoFit
End Select
End With
End Sub``````
Hope that helps

Denis

Denis,

this new code goes on to the sheet that has the Target range on? Am I correct? And then you Set Sht to the the sheet that contains the ranges that are to be hidden/unhidden?

ajm

Got in in one :wink:

Denis

Denis,

It just won't work for me. Can I be a real pain and PM or email you my working book? Its very small in size.

Andrew

Replies
1
Views
287
Replies
5
Views
474
Replies
3
Views
206
Replies
0
Views
1K
Replies
7
Views
287

1,202,962
Messages
6,052,805
Members
444,602
Latest member
Cookaa

### 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.

### Which adblocker are you using?

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

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