unhide rows based on value of particular cell?

[ajm]

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

 

[SydneyGeek]

Andrew,

I sent you a PM with my email address.

Denis

 

[ajm]

denis,

trying to modify this one for something else and am curious. Can the target values be alphas instead of numerics? My need this time stems from users picking their name out of a drop down list and once selected, all other sections/rows will be hidden. So, the Intersect(Target, Range("f172") will contain a drop down list of operator names eg Qld Rail, Bribie, Brisbane Transport, BCC Ferries, Buslink, Caboolture, ...etc

On selecting "Qld Rail" all the other sections will remain hidden. is there a way to say:

"Private Sub Worksheet_Change(ByVal Target As Range)

If Target.Cells.Count > 1 Then Exit Sub
If Intersect(Target, Range("f172")) Is Nothing Then Exit Sub
Rows("187:2786").RowHeight = 0
Select Case Target.Value

Case "Qld Rail"
.Rows("187:339").AutoFit
etc etc etc"

ajm

 

[SydneyGeek]

Hi Andrew,

The structure you suggested should work just fine.

Denis

 

[ajm]

Denis,

any clue why this one doesn't do the job?
first tried it as a Sub () and then as a Worksheet Change - neither seems to work?

F172 has a list of operators in a drop down box, when its changed to a particular operator, we only want that operator's rows below it to be displayed.

Private Sub Worksheet_Change(ByVal Target As Range)
Dim Sht As Worksheet
If Target.Cells.Count > 1 Then Exit Sub
If Intersect(Target, Range("f172")) Is Nothing Then Exit Sub
Set Sht = Sheets("Parameters and Assumptions")
With Sht
.Rows("187:2786").RowHeight = 0
ElseIf Target = "QRail" Then
.Rows("187:339").AutoFit
ElseIf Target = "Bribie" Then
.Rows("340:492").AutoFit
ElseIf Target = "BrisbaneTransport" Then
.Rows("493:645").AutoFit
ElseIf Target = "BCCFerries" Then
.Rows("646:798").AutoFit
ElseIf Target = "Buslink" Then
.Rows("799:951").AutoFit
ElseIf Target = "Caboolture" Then
.Rows("952:1104").AutoFit
ElseIf Target = "Clarks" Then
.Rows("1105:1257").AutoFit
ElseIf Target = "Hornibrook" Then
.Rows("1258:1410").AutoFit
ElseIf Target = "Kangaroo" Then
.Rows("1411:1563").AutoFit
ElseIf Target = "MtGravatt" Then
.Rows("1564:1716").AutoFit
ElseIf Target = "National" Then
.Rows("1717:1869").AutoFit
ElseIf Target = "ParkRidge" Then
.Rows("1870:2022").AutoFit
ElseIf Target = "Sunbus" Then
.Rows("2023:2175").AutoFit
ElseIf Target = "Thompson" Then
.Rows("2176:2328").AutoFit
ElseIf Target = "Westside" Then
.Rows("2329:2481").AutoFit
ElseIf Target = "SouthernCross" Then
.Rows("2482:2634").AutoFit
ElseIf Target = "Surfside" Then
.Rows("2635:2787").AutoFit
ElseIf Target = "AllOPerators" Then
.Rows("187:2940").AutoFit
End If
End Sub

 

[ajm]

denis, stress not. got it running with some help from Jindon. cheers big ears.

 

[SydneyGeek]

Hi Andrew,

Glad you got it sorted. Just got aroiund to seeing the code -- you needed Select Case instead of all the ElseIf statements.

Denis

 

[jindon]

ajm
I'd rather reply here to your question in the other thread

I would do like this and assuming the code is in "Parameters and Assumptions" sheet module

Option Compare Text

Private Sub Worksheet_Change(ByVal Target As Range)
Dim myRows As String
If Target.Cells.Count > 1 Then Exit Sub
    If Intersect(Target, Range("f172")) Is Nothing Then Exit Sub
With Me
   .Rows("187:2786").RowHeight = 0
   Select Case Target.Value
      Case "QRail" : myRows = "187:339"
      Case "Bribie" : myRows = "340:492"
      Case "BrisbaneTransport" : "493:645"
      Case "BCCFerries" : "646:798" 
      Case "Buslink" : myRows = "799:951"
      Case "Caboolture" : myRows = "952:1104"
      Case "Clarks" : myRows = "1105:1257"
      Case "Hornibrook" : myRows = "1258:1410"
      Case "Kangaroo" : myRows = "1411:1563"
      Case "MtGravatt" : myRows = "1564:1716"
      Case "National" : myRows = "1717:1869"
      Case "ParkRidge" : myRows = "1870:2022"
      Case "Sunbus" : myRows = "2023:2175"
      Case "Thompson" : myRows = "2176:2328"
      Case "Westside" : myRows = "2329:2481"
      Case "SouthernCross" : "2482:2634"
      Case "Surfside" : myRows = "2635:2787"
      Case "AllOPerators" : myRows = "187:2940"
   End Select
   If Len(myRows) Then .Rows(myRows).AutoFit
End With
End Sub

 

[ajm]

thanks jindon. "Case "QRail": myRows = "187:339"" is where the proble lies. all other rows appear as selected. any ideas on this?

 

[jindon]

Possibly the word "QRail" in the list contains extra space, so try

Select Case Trim(Target.Value)

 

[ajm]

good point. I had QldRail instead of QRail. **** decaffeinated coffee must have been put in the regular coffee jar again....

many thanks.