unhide rows based on value of particular cell?

A

ajm

Well-known Member
Joined
Feb 5, 2003
Messages
2,056
Office Version
  1. 365
Platform
  1. Windows
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
 
Sort by date Sort by votes
further to this query, can you have two or more worksheet_change events on the one page? if so, what is the naming syntax for the events fter the initial one?

am trying to use the code supplied by jindon, above, for a further section based on the same names, different target intersect range, but this time each section is only a one liner. check out the code below and perhaps somebody could tell me why its not running.

Private Sub Worksheet_Change_one(ByVal Target As Range)
Dim myRows As String
If Target.Cells.Count > 1 Then Exit Sub
If Intersect(Target, Range("a77")) Is Nothing Then Exit Sub
With Me
.Rows("78:95").RowHeight = 0
Select Case Target.Value
Case "QRail": myRows = "78:95"
Case "Bribie": myRows = "79:79"
Case "BrisbaneTransport": myRows = "80:80"
Case "BCCFerries": myRows = "81:81"
Case "Buslink": myRows = "82:82"
Case "Caboolture": myRows = "83:83"
Case "Clarks": myRows = "84:84"
Case "Hornibrook": myRows = "85:85"
Case "Kangaroo": myRows = "86:86"
Case "MtGravatt": myRows = "87:87"
Case "National": myRows = "88:88"
Case "ParkRidge": myRows = "89:89"
Case "Sunbus": myRows = "90:90"
Case "Thompson": myRows = "91:91"
Case "Westside": myRows = "92:92"
Case "SouthernCross": myRows = "93:93"
Case "Surfside": myRows = "94:94"
Case "BBL": myRows = "95:95"
Case "AllOperators": myRows = "78:95"
End Select
If Len(myRows) Then .Rows(myRows).AutoFit
End With
End Sub
 
Upvote 0

Excel Facts

Which Excel functions can ignore hidden rows?
The SUBTOTAL and AGGREGATE functions ignore hidden rows. AGGREGATE can also exclude error cells and more.
further to this query, can you have two or more worksheet_change events on the one page? if so, what is the naming syntax for the events fter the initial one?
Click to expand...

Here's one way..

Code: 
Private Sub Worksheet_Change_one(ByVal Target As Range)
Dim myRows As String
If Target.Cells.Count > 1 Then Exit Sub
    If Not Intersect(Target, Range("a77")) Is Nothing Then
   With Me
   .Rows("78:95").RowHeight = 0
   Select Case Target.Value
      Case "QRail": myRows = "78:95"
      Case "Bribie": myRows = "79:79"
      Case "BrisbaneTransport": myRows = "80:80"
      Case "BCCFerries": myRows = "81:81"
      Case "Buslink": myRows = "82:82"
      Case "Caboolture": myRows = "83:83"
      Case "Clarks": myRows = "84:84"
      Case "Hornibrook": myRows = "85:85"
      Case "Kangaroo": myRows = "86:86"
      Case "MtGravatt": myRows = "87:87"
      Case "National": myRows = "88:88"
     Case "ParkRidge": myRows = "89:89"
      Case "Sunbus": myRows = "90:90"
      Case "Thompson": myRows = "91:91"
      Case "Westside": myRows = "92:92"
     Case "SouthernCross": myRows = "93:93"
      Case "Surfside": myRows = "94:94"
      Case "BBL": myRows = "95:95"
    Case "AllOperators": myRows = "78:95"
   End Select
   If Len(myRows) Then .Rows(myRows).AutoFit
   End With

  ElseIf Not Intersect(Target,Range("NewRange")) Is Nothing Then
     'New code here
  End If
End Sub
 
Upvote 0
jindon, am confused. can this be pasted in directly beneath the code you gave me the other day? and what '"new code" should be pasted into the Elseif at the bottom of the code?
 
Upvote 0
Ah..
I thin I misread the question.

Do you want to expand the triger range?
If so

Change like

If Intersect(Target,Range("a1:d1")) Is Nothing Then

If Intersect(Target,Range("a77,b21,d33")) Is Nothing Then

If InterSect(Target,Range("a:a,d:f")) Is Nothing Then

Is this what you wanted?

Otherwise can you post your "Other code"?
 
Upvote 0
no expansion to the trigger range. it is still only going to be the one cell. this cell contains a drop down list of company names. when the comapny's name is selected, all other rows should remain hidden. (Sub Worksheet_Change_one)

This is on the same tab as the code you assisted with the other day. this also relied on the change in a particular cell to hide/unhide associated number of rows below. (Sub Worksheet_Change)

Private Sub Worksheet_Change(ByVal Target As Range)
Dim myRows As String
If Target.Cells.Count > 1 Then Exit Sub
If Intersect(Target, Range("b172")) Is Nothing Then Exit Sub
With Me
.Rows("187:2940").RowHeight = 0
Select Case Target.Value
Case "QRail": myRows = "187:339"
Case "Bribie": myRows = "340:492"
Case "BrisbaneTransport": myRows = "493:645"
Case "BCCFerries": myRows = "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": myRows = "2482:2634"
Case "Surfside": myRows = "2635:2787"
Case "BBL": myRows = "2788:2938"
Case "AllOperators": myRows = "187:2940"
End Select
If Len(myRows) Then .Rows(myRows).AutoFit
End With
End Sub

Private Sub Worksheet_Change_one(ByVal Target As Range)
Dim myRows As String
If Target.Cells.Count > 1 Then Exit Sub
If Intersect(Target, Range("a77")) Is Nothing Then Exit Sub
With Me
.Rows("78:95").RowHeight = 0
Select Case Target.Value
Case "QRail": myRows = "78:78"
Case "Bribie": myRows = "79:79"
Case "BrisbaneTransport": myRows = "80:80"
Case "BCCFerries": myRows = "81:81"
Case "Buslink": myRows = "82:82"
Case "Caboolture": myRows = "83:83"
Case "Clarks": myRows = "84:84"
Case "Hornibrook": myRows = "85:85"
Case "Kangaroo": myRows = "86:86"
Case "MtGravatt": myRows = "87:87"
Case "National": myRows = "88:88"
Case "ParkRidge": myRows = "89:89"
Case "Sunbus": myRows = "90:90"
Case "Thompson": myRows = "91:91"
Case "Westside": myRows = "92:92"
Case "SouthernCross": myRows = "93:93"
Case "Surfside": myRows = "94:94"
Case "BBL": myRows = "95:95"
Case "AllOperators": myRows = "78:95"
End Select
If Len(myRows) Then .Rows(myRows).AutoFit
End With
End Sub
 
Upvote 0
OK

should be like this
Code: 
Private Sub Worksheet_Change(ByVal Target As Range)
Dim myRows As String
If Target.Cells.Count > 1 Then Exit Sub
If Not Intersect(Target, Range("b172")) Is Nothing Then 
With Me
   .Rows("187:2940").RowHeight = 0
   Select Case Target.Value
      Case "QRail": myRows = "187:339"
      Case "Bribie": myRows = "340:492"
      Case "BrisbaneTransport": myRows = "493:645"
      Case "BCCFerries": myRows = "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": myRows = "2482:2634"
      Case "Surfside": myRows = "2635:2787"
      Case "BBL": myRows = "2788:2938"
      Case "AllOperators": myRows = "187:2940"
   End Select
   If Len(myRows) Then .Rows(myRows).AutoFit
End With
Else If Not Intersect(Target, Range("a77")) Is Nothing Then
With Me
   .Rows("78:95").RowHeight = 0
        Select Case Target.Value
    Case "QRail": myRows = "78:78"
    Case "Bribie": myRows = "79:79"
      Case "BrisbaneTransport": myRows = "80:80"
     Case "BCCFerries": myRows = "81:81"
    Case "Buslink": myRows = "82:82"
   Case "Caboolture": myRows = "83:83"
      Case "Clarks": myRows = "84:84"
     Case "Hornibrook": myRows = "85:85"
     Case "Kangaroo": myRows = "86:86"
     Case "MtGravatt": myRows = "87:87"
     Case "National": myRows = "88:88"
     Case "ParkRidge": myRows = "89:89"
     Case "Sunbus": myRows = "90:90"
     Case "Thompson": myRows = "91:91"
    Case "Westside": myRows = "92:92"
     Case "SouthernCross": myRows = "93:93"
     Case "Surfside": myRows = "94:94"
     Case "BBL": myRows = "95:95"
     Case "AllOperators": myRows = "78:95"
   End Select
   If Len(myRows) Then .Rows(myRows).AutoFit
End With
End If
End Sub
 
Upvote 0
I forgot to mention that the two intersect cells A77 and B172 are totally mutually exclusive. will this matter?

nevermind. it works well. i don't really understand the difference between the "If intersect " and the "If NOT intersect" that you use in this latest version. can you point me to some good reading material?
 
Upvote 0
I forgot to mention that the two intersect cells A77 and B172 are totally mutually exclusive. will this matter?

nevermind. it works well. i don't really understand the difference between the "If intersect " and the "If NOT intersect" that you use in this latest version. can you point me to some good reading material?
Click to expand...

Intersect method returns if 2 ranges are partly/fully overlaps...
So
Not Intersect(Target,Range("a77")) Is Nothing Then
means if Target is overlapping with Range("a77") Then

whereas If Intersect(Target,Range("a77")) Is Nothing Then
means if Target is not overlapping with range("a77") Then

I used this, because your original code used it.

I would write like this in your case

If Target.Address(0,0) = "A77" Then
.
.
.
.
ElseIf Target.Address(0,0) = "B172" Then
.
.
.
End If
 
Upvote 0
You must log in or register to reply here.

Similar threads

F
  • Solved
VBA to Automatically Hide/Unhide Dynamic Range based Upon Cell Value
Replies
3
Views
353
franswa3434
F
G
  • Solved
If Then else in Excel
Replies
9
Views
366
Gulfcourse
G
B
  • Question Question
link a VBA checkbox to a Cell to change value
Replies
6
Views
680
NoSparks
N
N
  • Question Question
Hiding rows based on cell value
Replies
3
Views
2K
ninjapaping
ninjapaping
P
  • Question Question
vba question
Replies
6
Views
224
Pinkyhello
P

Forum statistics

Threads
1,224,885
Messages
6,181,586
Members
453,055
Latest member
cope7895

Share this page

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