VBA Code Hide / Unhide Rows based on selection of 2x Drop Down Lists

tlc53

tlc53

Active Member
Joined
Jul 26, 2018
Messages
399
Hi there,

I have the below code, which is working well at hiding and unhiding rows depending on the number of investments selected from the drop down list.

In additional to my current code, I was wondering if I could go a step further and hide further rows depending on the outcome of 2x drop down lists.

For eg.
Drop down list "Entity Type"
Drop down list "No. of Investments"

If "Entity Type" <>"Partnership"
Hide rows (165:173,178:186,191:199,204:212,217:225,230:238,243:251,256:264,269:277,282,290)

If "Entity Type" = "Partnership" &
"No. of Investments" = "Please Select" or 0
Hide rows (165:173,178:186,191:199,204:212,217:225,230:238,243:251,256:264,269:277,282,290)

If "Entity Type" = "Partnership" &
"No. of Investments" = 2
Hide rows (191:199,204:212,217:225,230:238,243:251,256:264,269:277,282,290)

If "Entity Type" = "Partnership" &
"No. of Investments" = 4
Hide rows (217:225,230:238,243:251,256:264,269:277,282,290)

And so on for 6, 8 and 10 Investments.

I'd still like to use the below code, and add the above in addition.

I'd be most grateful if you could tell me whether this is possible. I'm still a beginner when it comes to VBA coding.

Thank you!

Private Sub Worksheet_Change(ByVal Target As Range)


If Not Intersect(Target, Range("No._Investments")) Is Nothing Then


If Target.Cells.CountLarge > 1 Then Exit Sub
Select Case Target.Value


Case "Please Select"
Range("26:136,163:292").EntireRow.Hidden = True

Case 0
Range("26:136,163:292").EntireRow.Hidden = True

Case 2
Range("26:136,163:292").EntireRow.Hidden = False
Range("50:136,189:292").EntireRow.Hidden = True

Case 4
Range("26:136,163:292").EntireRow.Hidden = False
Range("72:136,215:292").EntireRow.Hidden = True

Case 6
Range("26:136,163:292").EntireRow.Hidden = False
Range("94:136,241:292").EntireRow.Hidden = True

Case 8
Range("26:136,163:292").EntireRow.Hidden = False
Range("116:136,267:292").EntireRow.Hidden = True

Case 10
Range("26:136,163:292").EntireRow.Hidden = False
Range("136:136,292:292").EntireRow.Hidden = True

End Select
End If
End Sub
 

Excel Facts

Do you hate GETPIVOTDATA?
Prevent GETPIVOTDATA. Select inside a PivotTable. In the Analyze tab of the ribbon, open the dropown next to Options and turn it off
Sort by date Sort by votes
Thank you, I appreciate the offer! No quite sure what "code between the CODE tags" are though.
However, the more I look at what I'm trying to achieve, the more I'm thinking about putting it into the too hard basket. I would actually need to base it on No. of Investments and No. of Partners, and they'd be atleast 80 different scenarios. It's only to hide rows to make it look tidier, so I don't think I'll go down that route now. It'll probably slow it down terribly too.
I've not seen code written taking into account 2x drop down lists but I gather from you, it is possible, which is interesting.
I think I'll just leave it, but thanks again! :)
 
Upvote 0
When you're either creating a thread or replying to a thread, there are icons above the text box. Paste your code, highlight it all, and click on the # icon. This puts your code in a scroll-able box that keeps the tabular format used in the VBA editing window.

I believe I already have a solution to your request. I just don't like spending time reformatting the code. It's easier for me to paste and edit your code in VBA.

Jeff
 
Upvote 0
Thanks for explaining the code wrap function to me. I'll be sure to use that in future!
I've attached my idea of what the criteria would be but it's rather long winded. If it's too much work, please don't worry about it.
I've grouped it by "No of Investments" and listed the change by "No. of Partners" selected.

Thanks!

Code: 
Drop down list "No. of Investments"
Drop down list "No. of Partners"


[B][U]If “No. of Investments” = 2 &[/U][/B]
If “No. of Partners” = 2 
Hide rows (166:173,179:186)
If “No. of Partners” = 3
Hide rows (167:173,180:186)
If “No. of Partners” = 4
Hide rows (168:173,181:186)
If “No. of Partners” = 5
Hide rows (169:173,182:186)
If “No. of Partners” = 6
Hide rows (170:173,183:186)
If “No. of Partners” = 7
Hide rows (171:173,184:186)
If “No. of Partners” = 8
Hide rows (172:173,185:186)
If “No. of Partners” = 9
Hide rows (173,186)
 
[B][U]If “No. of Investments” = 4 &[/U][/B]
If “No. of Partners” = 2 &
Hide rows (166:173,179:186,192:199,205:212)
If “No. of Partners” = 3
Hide rows (167:173,180:186,193:199,206:212)
If “No. of Partners” = 4
Hide rows (168:173,181:186,194:199,207:212)
If “No. of Partners” = 5
Hide rows (169:173,182:186,195:199,208:212)
If “No. of Partners” = 6
Hide rows (170:173,183:186,196:199,209:212)
If “No. of Partners” = 7
Hide rows (171:173,184:186,197:199,210:212)
If “No. of Partners” = 8
Hide rows (172:173,185:186,198:199,211:212)
If “No. of Partners” = 9
Hide rows (173,186,199,212)
 
[B][U]If “No. of Investments” = 6 &[/U][/B]
If “No. of Partners” = 2 &
Hide rows (166:173,179:186,192:199,205:212,218:225,231:238)
If “No. of Partners” = 3
Hide rows (167:173,180:186,193:199,206:212,219:225,232:238)
If “No. of Partners” = 4
Hide rows (168:173,181:186,194:199,207:212,220:225,233:238)
If “No. of Partners” = 5
Hide rows (169:173,182:186,195:199,208:212,221:225,234:238)
If “No. of Partners” = 6
Hide rows (170:173,183:186,196:199,209:212,222:225,235:238)
If “No. of Partners” = 7
Hide rows (171:173,184:186,197:199,210:212,223:225,236:238)
If “No. of Partners” = 8
Hide rows (172:173,185:186,198:199,211:212,224:225,237:238)
If “No. of Partners” = 9
Hide rows (173,186,199,212,225,238)
 
[B][U]If “No. of Investments” = 8 &[/U][/B]
If “No. of Partners” = 2 &
Hide rows (166:173,179:186,192:199,205:212,218:225,231:238,244:251,257:264)
If “No. of Partners” = 3
Hide rows (167:173,180:186,193:199,206:212,219:225,232:238,245:251,258:264)
If “No. of Partners” = 4
Hide rows (168:173,181:186,194:199,207:212,220:225,233:238,246:251,259:264)
If “No. of Partners” = 5
Hide rows (169:173,182:186,195:199,208:212,221:225,234:238,247:251,260:264)
If “No. of Partners” = 6
Hide rows (170:173,183:186,196:199,209:212,222:225,235:238,248:251,261:264)
If “No. of Partners” = 7
Hide rows (171:173,184:186,197:199,210:212,223:225,236:238,249:251,262:264)
If “No. of Partners” = 8
Hide rows (172:173,185:186,198:199,211:212,224:225,237:238,250:251,263:264)
If “No. of Partners” = 9
Hide rows (173,186,199,212,225,238,251,264)
 
[B][U]If “No. of Investments” = 10 &[/U][/B]
If “No. of Partners” = 2 &
Hide rows (166:173,179:186,192:199,205:212,218:225,231:238,244:251,257:264,270:277,283:290)
If “No. of Partners” = 3
Hide rows (167:173,180:186,193:199,206:212,219:225,232:238,245:251,258:264,271:277,284:290)
If “No. of Partners” = 4
Hide rows (168:173,181:186,194:199,207:212,220:225,233:238,246:251,259:264,272:277,285:290)
If “No. of Partners” = 5
Hide rows (169:173,182:186,195:199,208:212,221:225,234:238,247:251,260:264,273:277,286:290)
If “No. of Partners” = 6
Hide rows (170:173,183:186,196:199,209:212,222:225,235:238,248:251,261:264,274:277,287:290)
If “No. of Partners” = 7
Hide rows (171:173,184:186,197:199,210:212,223:225,236:238,249:251,262:264,275:277,288:290)
If “No. of Partners” = 8
Hide rows (172:173,185:186,198:199,211:212,224:225,237:238,250:251,263:264,276:277,289:290)
If “No. of Partners” = 9
Hide rows (173,186,199,212,225,238,251,264,277,290)
 
Upvote 0
Here is a framework of code based on your last post. Any where there is a "?", you will need to fill in the actual row numbers. There's a lot and I didn't feel like copying them all into the code.

The Union command allows the ranges to kept separate even though they're combined into one range.

Let me know if you have more questions.

Jeff

Code: 
Private Sub Worksheet_Change(ByVal Target As Range)


  Dim i As Range
  Dim R As Range
  Dim InvSel As Integer
  Dim PartSel As Integer
  Dim InvestRng As Range
  Dim PartnerRng As Range
  
  Set InvestRng = Range("No._Investments")
  Set PartnerRng = Range("NoPartners")
  
  Set i = Intersect(Target, InvestRng)
  If Not i Is Nothing Then
    InvSel = InvestRng.Value
    PartSel = PartnerRng.Value
    Select Case InvSel
      Case 2
        If PartSel = 2 Then
          Set R = Union(Range("166:173"), Range("179:186"))
        ElseIf PartSel = 3 Then
          Set R = Union(Range("167:173"), Range("180:186"))
        ElseIf PartSel = 4 Then
          Set R = Union(Range("168:173"), Range("181:186"))
        ElseIf PartSel = 5 Then
          Set R = Union(Range("169:173"), Range("182:186"))
        ElseIf PartSel = 6 Then
          Set R = Union(Range("170:173"), Range("183:186"))
        ElseIf PartSel = 7 Then
          Set R = Union(Range("171:173"), Range("184:186"))
        ElseIf PartSel = 8 Then
          Set R = Union(Range("172:173"), Range("185:186"))
        ElseIf PartSel = 9 Then
          Set R = Union(Range("173"), Range("186"))
        End If
      Case 4
        If PartSel = 2 Then
          Set R = Union(Range("166:173"), Range("179:186"), Range("192:199"), Range("205:212"))
        ElseIf PartSel = 3 Then
          Set R = Union(Range("167:173"), Range("180:186"), Range("193:199"), Range("206:212"))
        ElseIf PartSel = 4 Then
          Set R = Union(Range("168:173"), Range("181:186"), Range("194:199"), Range("207:212"))
        ElseIf PartSel = 5 Then
          
          Set R = Union(Range("???:???"), Range("???:???"), Range("???:???"), Range("???:???"))
        ElseIf PartSel = 6 Then
          Set R = Union(Range("???:???"), Range("???:???"), Range("???:???"), Range("???:???"))
        ElseIf PartSel = 7 Then
          Set R = Union(Range("???:???"), Range("???:???"), Range("???:???"), Range("???:???"))
        ElseIf PartSel = 8 Then
          Set R = Union(Range("???:???"), Range("???:???"), Range("???:???"), Range("???:???"))
        ElseIf PartSel = 9 Then
          Set R = Union(Range("???"), Range("???"))
        End If
      Case 6
        If PartSel = 2 Then
          Set R = Union(Range("???:???"), Range("???:???"), Range("???:???"), Range("???:???"))
        ElseIf PartSel = 3 Then
          Set R = Union(Range("???:???"), Range("???:???"), Range("???:???"), Range("???:???"))
        ElseIf PartSel = 4 Then
          Set R = Union(Range("???:???"), Range("???:???"), Range("???:???"), Range("???:???"))
        ElseIf PartSel = 5 Then
          Set R = Union(Range("???:???"), Range("???:???"), Range("???:???"), Range("???:???"))
        ElseIf PartSel = 6 Then
          Set R = Union(Range("???:???"), Range("???:???"), Range("???:???"), Range("???:???"))
        ElseIf PartSel = 7 Then
          Set R = Union(Range("???:???"), Range("???:???"), Range("???:???"), Range("???:???"))
        ElseIf PartSel = 8 Then
          Set R = Union(Range("???:???"), Range("???:???"), Range("???:???"), Range("???:???"))
        ElseIf PartSel = 9 Then
          Set R = Union(Range("???"), Range("???"))
        End If
      Case 8
        If PartSel = 2 Then
          Set R = Union(Range("???:???"), Range("???:???"), Range("???:???"), Range("???:???"))
        ElseIf PartSel = 3 Then
          Set R = Union(Range("???:???"), Range("???:???"), Range("???:???"), Range("???:???"))
        ElseIf PartSel = 4 Then
          Set R = Union(Range("???:???"), Range("???:???"), Range("???:???"), Range("???:???"))
        ElseIf PartSel = 5 Then
          Set R = Union(Range("???:???"), Range("???:???"), Range("???:???"), Range("???:???"))
        ElseIf PartSel = 6 Then
          Set R = Union(Range("???:???"), Range("???:???"), Range("???:???"), Range("???:???"))
        ElseIf PartSel = 7 Then
          Set R = Union(Range("???:???"), Range("???:???"), Range("???:???"), Range("???:???"))
        ElseIf PartSel = 8 Then
          Set R = Union(Range("???:???"), Range("???:???"), Range("???:???"), Range("???:???"))
        ElseIf PartSel = 9 Then
          Set R = Union(Range("???"), Range("???"))
        End If
      Case 10
        If PartSel = 2 Then
          Set R = Union(Range("???:???"), Range("???:???"), Range("???:???"), Range("???:???"))
        ElseIf PartSel = 3 Then
          Set R = Union(Range("???:???"), Range("???:???"), Range("???:???"), Range("???:???"))
        ElseIf PartSel = 4 Then
          Set R = Union(Range("???:???"), Range("???:???"), Range("???:???"), Range("???:???"))
        ElseIf PartSel = 5 Then
          Set R = Union(Range("???:???"), Range("???:???"), Range("???:???"), Range("???:???"))
        ElseIf PartSel = 6 Then
          Set R = Union(Range("???:???"), Range("???:???"), Range("???:???"), Range("???:???"))
        ElseIf PartSel = 7 Then
          Set R = Union(Range("???:???"), Range("???:???"), Range("???:???"), Range("???:???"))
        ElseIf PartSel = 8 Then
          Set R = Union(Range("???:???"), Range("???:???"), Range("???:???"), Range("???:???"))
        ElseIf PartSel = 9 Then
          Set R = Union(Range("???"), Range("???"))
        End If
    End Select
      
    If Not R Is Nothing Then R.EntireRow.Hidden = True
  End If
End Sub
 
Upvote 0
Hi Jeff,

I amended and added the code but it doesn't seem to work. I'm not sure if it has anything to do with my other code already in there. I also added the criteria "Please Select", which hides all lines but not 100% sure I did this right. Can you see where I'm going wrong? Thanks!



Code: 
Private Sub Worksheet_Change(ByVal Target As Range)


If Not Intersect(Target, Range("No._Investments")) Is Nothing Then


If Target.Cells.CountLarge > 1 Then Exit Sub
Select Case Target.Value


    Case "Please Select"
        Range("26:136,163:292,139:149").EntireRow.Hidden = True
        
    Case 0
        Range("26:136,163:292,139:149").EntireRow.Hidden = True
        
    Case 2
        Range("26:136,163:292,139:149").EntireRow.Hidden = False
        Range("50:136,189:292,142:149").EntireRow.Hidden = True
        
    Case 4
        Range("26:136,163:292,139:149").EntireRow.Hidden = False
        Range("72:136,215:292,144:149").EntireRow.Hidden = True
          
    Case 6
        Range("26:136,163:292,139:149").EntireRow.Hidden = False
        Range("94:136,241:292,146:149").EntireRow.Hidden = True
        
    Case 8
        Range("26:136,163:292,139:149").EntireRow.Hidden = False
        Range("116:136,267:292,148:149").EntireRow.Hidden = True
        
    Case 10
        Range("26:136,163:292,139:149").EntireRow.Hidden = False
          
        End Select
        End If
        
        If Not Intersect(Target, Range("No._Partners")) Is Nothing Then


If Target.Cells.CountLarge > 1 Then Exit Sub
Select Case Target.Value


    Case "Please Select"
        Range("11:25").EntireRow.Hidden = True
        
    Case 2
        Range("11:25").EntireRow.Hidden = False
        Range("16:23").EntireRow.Hidden = True
        
    Case 3
        Range("11:25").EntireRow.Hidden = False
        Range("17:23").EntireRow.Hidden = True
          
    Case 4
        Range("11:25").EntireRow.Hidden = False
        Range("18:23").EntireRow.Hidden = True
        
    Case 5
        Range("11:25").EntireRow.Hidden = False
        Range("19:23").EntireRow.Hidden = True
        
    Case 6
        Range("11:25").EntireRow.Hidden = False
        Range("20:23").EntireRow.Hidden = True
        
    Case 7
        Range("11:25").EntireRow.Hidden = False
        Range("21:23").EntireRow.Hidden = True
        
    Case 8
        Range("11:25").EntireRow.Hidden = False
        Range("22:23").EntireRow.Hidden = True
        
    Case 9
        Range("11:25").EntireRow.Hidden = False
        Range("23").EntireRow.Hidden = True
    
    Case 10
        Range("11:25").EntireRow.Hidden = False
                
        End Select
        End If
        
  Dim i As Range
  Dim R As Range
  Dim InvSel As Integer
  Dim PartSel As Integer
  Dim InvestRng As Range
  Dim PartnerRng As Range
  
  Set InvestRng = Range("No._Investments")
  Set PartnerRng = Range("No._Partners")
  
  Set i = Intersect(Target, InvestRng)
  If Not i Is Nothing Then
    InvSel = InvestRng.Value
    PartSel = PartnerRng.Value
    Select Case InvSel
      Case 2
        If PartSel = "Please Select" Then
          Set R = Union(Range("165:173"), Range("178:186"))
        ElseIf PartSel = 2 Then
          Set R = Union(Range("166:173"), Range("179:186"))
        ElseIf PartSel = 3 Then
          Set R = Union(Range("167:173"), Range("180:186"))
        ElseIf PartSel = 4 Then
          Set R = Union(Range("168:173"), Range("181:186"))
        ElseIf PartSel = 5 Then
          Set R = Union(Range("169:173"), Range("182:186"))
        ElseIf PartSel = 6 Then
          Set R = Union(Range("170:173"), Range("183:186"))
        ElseIf PartSel = 7 Then
          Set R = Union(Range("171:173"), Range("184:186"))
        ElseIf PartSel = 8 Then
          Set R = Union(Range("172:173"), Range("185:186"))
        ElseIf PartSel = 9 Then
          Set R = Union(Range("173"), Range("186"))
        End If
      
      Case 4
        If PartSel = "Please Select" Then
          Set R = Union(Range("165:173"), Range("178:186"), Range("191:199"), Range("204:212"))
        ElseIf PartSel = 2 Then
          Set R = Union(Range("166:173"), Range("179:186"), Range("192:199"), Range("205:212"))
        ElseIf PartSel = 3 Then
          Set R = Union(Range("167:173"), Range("180:186"), Range("193:199"), Range("206:212"))
        ElseIf PartSel = 4 Then
          Set R = Union(Range("168:173"), Range("181:186"), Range("194:199"), Range("207:212"))
        ElseIf PartSel = 5 Then
          
          Set R = Union(Range("169:173"), Range("182:186"), Range("195:199"), Range("208:212"))
        ElseIf PartSel = 6 Then
          Set R = Union(Range("170:173"), Range("183:186"), Range("196:199"), Range("209:212"))
        ElseIf PartSel = 7 Then
          Set R = Union(Range("171:173"), Range("184:186"), Range("197:199"), Range("210:212"))
        ElseIf PartSel = 8 Then
          Set R = Union(Range("172:173"), Range("185:186"), Range("198:199"), Range("211:212"))
        ElseIf PartSel = 9 Then
          Set R = Union(Range("173"), Range("186"), Range("199"), Range("212"))
        End If
      Case 6
        If PartSel = "Please Select" Then
          Set R = Union(Range("165:173"), Range("178:186"), Range("191:199"), Range("204:212"), Range("217:225"), Range("230:238"))
        ElseIf PartSel = 2 Then
          Set R = Union(Range("166:173"), Range("179:186"), Range("192:199"), Range("205:212"), Range("218:225"), Range("231:238"))
        ElseIf PartSel = 3 Then
          Set R = Union(Range("167:173"), Range("180:186"), Range("193:199"), Range("206:212"), Range("219:225"), Range("232:238"))
        ElseIf PartSel = 4 Then
          Set R = Union(Range("168:173"), Range("181:186"), Range("194:199"), Range("207:212"), Range("220:225"), Range("233:238"))
        ElseIf PartSel = 5 Then
          Set R = Union(Range("169:173"), Range("182:186"), Range("195:199"), Range("208:212"), Range("221:225"), Range("234:238"))
        ElseIf PartSel = 6 Then
          Set R = Union(Range("170:173"), Range("183:186"), Range("196:199"), Range("209:212"), Range("222:225"), Range("235:238"))
        ElseIf PartSel = 7 Then
          Set R = Union(Range("171:173"), Range("184:186"), Range("197:199"), Range("210:212"), Range("223:225"), Range("236:238"))
        ElseIf PartSel = 8 Then
          Set R = Union(Range("172:173"), Range("185:186"), Range("198:199"), Range("211:212"), Range("224:225"), Range("237:238"))
        ElseIf PartSel = 9 Then
          Set R = Union(Range("173"), Range("186"), Range("199"), Range("212"), Range("225"), Range("238"))
        End If
      Case 8
        If PartSel = "Please Select" Then
          Set R = Union(Range("165:173"), Range("178:186"), Range("191:199"), Range("204:212"), Range("217:225"), Range("230:238"), Range("243:251"), Range("256:264"))
        ElseIf PartSel = 2 Then
          Set R = Union(Range("166:173"), Range("179:186"), Range("192:199"), Range("205:212"), Range("218:225"), Range("231:238"), Range("244:251"), Range("257:264"))
        ElseIf PartSel = 3 Then
          Set R = Union(Range("167:173"), Range("180:186"), Range("193:199"), Range("206:212"), Range("219:225"), Range("232:238"), Range("245:251"), Range("258:264"))
        ElseIf PartSel = 4 Then
          Set R = Union(Range("168:173"), Range("181:186"), Range("194:199"), Range("207:212"), Range("220:225"), Range("233:238"), Range("246:251"), Range("259:264"))
        ElseIf PartSel = 5 Then
          Set R = Union(Range("169:173"), Range("182:186"), Range("195:199"), Range("208:212"), Range("221:225"), Range("234:238"), Range("247:251"), Range("260:264"))
        ElseIf PartSel = 6 Then
          Set R = Union(Range("170:173"), Range("183:186"), Range("196:199"), Range("209:212"), Range("222:225"), Range("235:238"), Range("248:251"), Range("261:264"))
        ElseIf PartSel = 7 Then
          Set R = Union(Range("171:173"), Range("184:186"), Range("197:199"), Range("210:212"), Range("223:225"), Range("236:238"), Range("249:251"), Range("262:264"))
        ElseIf PartSel = 8 Then
          Set R = Union(Range("172:173"), Range("185:186"), Range("198:199"), Range("211:212"), Range("224:225"), Range("237:238"), Range("250:251"), Range("263:264"))
        ElseIf PartSel = 9 Then
          Set R = Union(Range("173"), Range("186"), Range("199"), Range("212"), Range("225"), Range("238"), Range("251"), Range("264"))
        End If
      Case 10
        If PartSel = "Please Select" Then
          Set R = Union(Range("165:173"), Range("178:186"), Range("191:199"), Range("204:212"), Range("217:225"), Range("230:238"), Range("243:251"), Range("256:264"), Range("269:277"), Range("282:290"))
        ElseIf PartSel = 2 Then
          Set R = Union(Range("166:173"), Range("179:186"), Range("192:199"), Range("205:212"), Range("218:225"), Range("231:238"), Range("244:251"), Range("257:264"), Range("270:277"), Range("283:290"))
        ElseIf PartSel = 3 Then
          Set R = Union(Range("167:173"), Range("180:186"), Range("193:199"), Range("206:212"), Range("219:225"), Range("231:238"), Range("245:251"), Range("258:264"), Range("271:277"), Range("284:290"))
        ElseIf PartSel = 4 Then
          Set R = Union(Range("168:173"), Range("181:186"), Range("194:199"), Range("207:212"), Range("220:225"), Range("231:238"), Range("246:251"), Range("259:264"), Range("272:277"), Range("285:290"))
        ElseIf PartSel = 5 Then
          Set R = Union(Range("169:173"), Range("182:186"), Range("195:199"), Range("208:212"), Range("221:225"), Range("231:238"), Range("247:251"), Range("260:264"), Range("273:277"), Range("286:290"))
        ElseIf PartSel = 6 Then
          Set R = Union(Range("170:173"), Range("183:186"), Range("196:199"), Range("209:212"), Range("222:225"), Range("231:238"), Range("248:251"), Range("261:264"), Range("274:277"), Range("287:290"))
        ElseIf PartSel = 7 Then
          Set R = Union(Range("171:173"), Range("184:186"), Range("197:199"), Range("210:212"), Range("223:225"), Range("231:238"), Range("249:251"), Range("262:264"), Range("275:277"), Range("288:290"))
        ElseIf PartSel = 8 Then
          Set R = Union(Range("172:173"), Range("185:186"), Range("198:199"), Range("211:212"), Range("224:225"), Range("231:238"), Range("250:251"), Range("263:264"), Range("276:277"), Range("289:290"))
        ElseIf PartSel = 9 Then
          Set R = Union(Range("173"), Range("186"), Range("199"), Range("212"), Range("225"), Range("238"), Range("251"), Range("264"), Range("277"), Range("290"))
     End If
    End Select
      
    If Not R Is Nothing Then R.EntireRow.Hidden = True
  End If
        
If [h7] = "YES" Then
Sheets("K1a").Visible = True
Else
Sheets("K1a").Visible = False
End If
End Sub
 
Upvote 0
Two things. Yes, the bottom part of your code is overriding the top part

The other thing, for me anyway, is the style of your code. What I'm about to suggest doesn't affect how the code operates, but how it is read.

Sections within IF/End IF; Do/Loop; Select Case/End Select; and For/Next, all need to be indented. Example:

Code: 
Sub ProperIndention()
  Dim X As Long
  Dim Y As Long
  
  If X = Y Then
    Select Case X
      Case 1
        'some code
      Case 2
        'some code
    End Select
  End If
  
  Do While X < Y
    'Some Code here
    'Some more code
    If X > Y Then
      'More
    End If
  Loop
  
  For X = 1 To 100
    Y = X * 147 / 0.25
    If Y > 1000 Then
      'Do something
    End If
  Next X
  
  
End Sub

When the code is indented properly, you can see where the beginning and ending are. My 2 cents. Sorry for being like a teacher.
 
Upvote 0
I'm not going to learn if someone doesn't tell me, so thanks very much. Originally I was lining up all the code because it looked pretty, but I've taken your comments on board :)
Thanks for all your help too!
 
Upvote 0
So I've taken out the bottom code that was over riding this code. If I don't have "Please Select" as an option in this code, then it kind of works. However, Please Select needs to be an option. Often number of partners drop down will not be selected and will remain on "Please Select". Have I written the "Please Select" into the code correctly? If I run the debug it takes me to this line.



Code: 
Private Sub Worksheet_Change(ByVal Target As Range)


    If Not Intersect(Target, Range("No._Investments")) Is Nothing Then


    If Target.Cells.CountLarge > 1 Then Exit Sub
        Select Case Target.Value


            Case "Please Select"
                Range("26:136,139:149,163:292").EntireRow.Hidden = True
        
            Case 0
                Range("26:136,139:149,163:292").EntireRow.Hidden = True
        
            Case 2
                Range("26:136,139:149,163:292").EntireRow.Hidden = False
                Range("50:136,142:149,189:292").EntireRow.Hidden = True
        
            Case 4
                Range("26:136,139:149,163:292").EntireRow.Hidden = False
                Range("72:136,144:149,215:292").EntireRow.Hidden = True
          
            Case 6
                Range("26:136,139:149,163:292").EntireRow.Hidden = False
                Range("94:136,146:149,241:292").EntireRow.Hidden = True
        
            Case 8
                Range("26:136,139:149,163:292").EntireRow.Hidden = False
                Range("116:136,148:149,267:292,").EntireRow.Hidden = True
        
            Case 10
                Range("26:136,163:292,139:149").EntireRow.Hidden = False
          
        End Select
        End If
        
Dim i As Range
  Dim R As Range
  Dim InvSel As Integer
  Dim PartSel As Integer
  Dim InvestRng As Range
  Dim PartnerRng As Range
  
  Set InvestRng = Range("No._Investments")
  Set PartnerRng = Range("No._Partners")
  
  Set i = Intersect(Target, InvestRng)
  If Not i Is Nothing Then
    InvSel = InvestRng.Value
    PartSel = PartnerRng.Value
    Select Case InvSel
      Case 2
        If PartSel = "Please Select" Then
          Set R = Union(Range("165:173"), Range("178:186"))
        ElseIf PartSel = 2 Then
          Set R = Union(Range("166:173"), Range("179:186"))
        ElseIf PartSel = 3 Then
          Set R = Union(Range("167:173"), Range("180:186"))
        ElseIf PartSel = 4 Then
          Set R = Union(Range("168:173"), Range("181:186"))
        ElseIf PartSel = 5 Then
          Set R = Union(Range("169:173"), Range("182:186"))
        ElseIf PartSel = 6 Then
          Set R = Union(Range("170:173"), Range("183:186"))
        ElseIf PartSel = 7 Then
          Set R = Union(Range("171:173"), Range("184:186"))
        ElseIf PartSel = 8 Then
          Set R = Union(Range("172:173"), Range("185:186"))
        ElseIf PartSel = 9 Then
          Set R = Union(Range("173"), Range("186"))
        End If
      
      Case 4
        If PartSel = "Please Select" Then
          Set R = Union(Range("165:173"), Range("178:186"), Range("191:199"), Range("204:212"))
        ElseIf PartSel = 2 Then
          Set R = Union(Range("166:173"), Range("179:186"), Range("192:199"), Range("205:212"))
        ElseIf PartSel = 3 Then
          Set R = Union(Range("167:173"), Range("180:186"), Range("193:199"), Range("206:212"))
        ElseIf PartSel = 4 Then
          Set R = Union(Range("168:173"), Range("181:186"), Range("194:199"), Range("207:212"))
        ElseIf PartSel = 5 Then
          
          Set R = Union(Range("169:173"), Range("182:186"), Range("195:199"), Range("208:212"))
        ElseIf PartSel = 6 Then
          Set R = Union(Range("170:173"), Range("183:186"), Range("196:199"), Range("209:212"))
        ElseIf PartSel = 7 Then
          Set R = Union(Range("171:173"), Range("184:186"), Range("197:199"), Range("210:212"))
        ElseIf PartSel = 8 Then
          Set R = Union(Range("172:173"), Range("185:186"), Range("198:199"), Range("211:212"))
        ElseIf PartSel = 9 Then
          Set R = Union(Range("173"), Range("186"), Range("199"), Range("212"))
        End If
      Case 6
        If PartSel = "Please Select" Then
          Set R = Union(Range("165:173"), Range("178:186"), Range("191:199"), Range("204:212"), Range("217:225"), Range("230:238"))
        ElseIf PartSel = 2 Then
          Set R = Union(Range("166:173"), Range("179:186"), Range("192:199"), Range("205:212"), Range("218:225"), Range("231:238"))
        ElseIf PartSel = 3 Then
          Set R = Union(Range("167:173"), Range("180:186"), Range("193:199"), Range("206:212"), Range("219:225"), Range("232:238"))
        ElseIf PartSel = 4 Then
          Set R = Union(Range("168:173"), Range("181:186"), Range("194:199"), Range("207:212"), Range("220:225"), Range("233:238"))
        ElseIf PartSel = 5 Then
          Set R = Union(Range("169:173"), Range("182:186"), Range("195:199"), Range("208:212"), Range("221:225"), Range("234:238"))
        ElseIf PartSel = 6 Then
          Set R = Union(Range("170:173"), Range("183:186"), Range("196:199"), Range("209:212"), Range("222:225"), Range("235:238"))
        ElseIf PartSel = 7 Then
          Set R = Union(Range("171:173"), Range("184:186"), Range("197:199"), Range("210:212"), Range("223:225"), Range("236:238"))
        ElseIf PartSel = 8 Then
          Set R = Union(Range("172:173"), Range("185:186"), Range("198:199"), Range("211:212"), Range("224:225"), Range("237:238"))
        ElseIf PartSel = 9 Then
          Set R = Union(Range("173"), Range("186"), Range("199"), Range("212"), Range("225"), Range("238"))
        End If
      Case 8
        If PartSel = "Please Select" Then
          Set R = Union(Range("165:173"), Range("178:186"), Range("191:199"), Range("204:212"), Range("217:225"), Range("230:238"), Range("243:251"), Range("256:264"))
        ElseIf PartSel = 2 Then
          Set R = Union(Range("166:173"), Range("179:186"), Range("192:199"), Range("205:212"), Range("218:225"), Range("231:238"), Range("244:251"), Range("257:264"))
        ElseIf PartSel = 3 Then
          Set R = Union(Range("167:173"), Range("180:186"), Range("193:199"), Range("206:212"), Range("219:225"), Range("232:238"), Range("245:251"), Range("258:264"))
        ElseIf PartSel = 4 Then
          Set R = Union(Range("168:173"), Range("181:186"), Range("194:199"), Range("207:212"), Range("220:225"), Range("233:238"), Range("246:251"), Range("259:264"))
        ElseIf PartSel = 5 Then
          Set R = Union(Range("169:173"), Range("182:186"), Range("195:199"), Range("208:212"), Range("221:225"), Range("234:238"), Range("247:251"), Range("260:264"))
        ElseIf PartSel = 6 Then
          Set R = Union(Range("170:173"), Range("183:186"), Range("196:199"), Range("209:212"), Range("222:225"), Range("235:238"), Range("248:251"), Range("261:264"))
        ElseIf PartSel = 7 Then
          Set R = Union(Range("171:173"), Range("184:186"), Range("197:199"), Range("210:212"), Range("223:225"), Range("236:238"), Range("249:251"), Range("262:264"))
        ElseIf PartSel = 8 Then
          Set R = Union(Range("172:173"), Range("185:186"), Range("198:199"), Range("211:212"), Range("224:225"), Range("237:238"), Range("250:251"), Range("263:264"))
        ElseIf PartSel = 9 Then
          Set R = Union(Range("173"), Range("186"), Range("199"), Range("212"), Range("225"), Range("238"), Range("251"), Range("264"))
        End If
      Case 10
        If PartSel = "Please Select" Then
          Set R = Union(Range("165:173"), Range("178:186"), Range("191:199"), Range("204:212"), Range("217:225"), Range("230:238"), Range("243:251"), Range("256:264"), Range("269:277"), Range("282:290"))
        ElseIf PartSel = 2 Then
          Set R = Union(Range("166:173"), Range("179:186"), Range("192:199"), Range("205:212"), Range("218:225"), Range("231:238"), Range("244:251"), Range("257:264"), Range("270:277"), Range("283:290"))
        ElseIf PartSel = 3 Then
          Set R = Union(Range("167:173"), Range("180:186"), Range("193:199"), Range("206:212"), Range("219:225"), Range("231:238"), Range("245:251"), Range("258:264"), Range("271:277"), Range("284:290"))
        ElseIf PartSel = 4 Then
          Set R = Union(Range("168:173"), Range("181:186"), Range("194:199"), Range("207:212"), Range("220:225"), Range("231:238"), Range("246:251"), Range("259:264"), Range("272:277"), Range("285:290"))
        ElseIf PartSel = 5 Then
          Set R = Union(Range("169:173"), Range("182:186"), Range("195:199"), Range("208:212"), Range("221:225"), Range("231:238"), Range("247:251"), Range("260:264"), Range("273:277"), Range("286:290"))
        ElseIf PartSel = 6 Then
          Set R = Union(Range("170:173"), Range("183:186"), Range("196:199"), Range("209:212"), Range("222:225"), Range("231:238"), Range("248:251"), Range("261:264"), Range("274:277"), Range("287:290"))
        ElseIf PartSel = 7 Then
          Set R = Union(Range("171:173"), Range("184:186"), Range("197:199"), Range("210:212"), Range("223:225"), Range("231:238"), Range("249:251"), Range("262:264"), Range("275:277"), Range("288:290"))
        ElseIf PartSel = 8 Then
          Set R = Union(Range("172:173"), Range("185:186"), Range("198:199"), Range("211:212"), Range("224:225"), Range("231:238"), Range("250:251"), Range("263:264"), Range("276:277"), Range("289:290"))
        ElseIf PartSel = 9 Then
          Set R = Union(Range("173"), Range("186"), Range("199"), Range("212"), Range("225"), Range("238"), Range("251"), Range("264"), Range("277"), Range("290"))
     End If
    End Select
      
    If Not R Is Nothing Then R.EntireRow.Hidden = True
  End If
       
          
If [h7] = "YES" Then
Sheets("K1a").Visible = True
Else
Sheets("K1a").Visible = False
End If
End Sub
 
Upvote 0
You must log in or register to reply here.

Similar threads

M
  • Question
Hide Rows based on cell with drop down values
Replies
1
Views
377
kevin9999
K
B
  • Solved
Another VBA question to hide/unhide rows based on cell value in another sheet
2
Replies
12
Views
379
kevin9999
K
J
  • Question
VBA Macro for hiding and unhiding rows is rearranging cells
2
Replies
14
Views
919
dmt32
D
D
  • Question
VBA to hide rows when a cell is a specific value and then hide the associated worksheet
Replies
3
Views
338
NoSparks
N
C
  • Question
Please help with VBA code with hide & unhide different rows
2
Replies
13
Views
1K
Akuini
Akuini

Forum statistics

Threads
1,214,545
Messages
6,120,132
Members
448,947
Latest member
test111

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