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
 
Sort by date Sort by votes
Make sure the cell actually contains "Please Select", because, yes, it should work.

You still have overlap in your code. For instance: You code at the top that tests for a change in Range("No._Investments") is going to hide rows "50:136,142:149,189:292" if it equals 2 and the code I created below where it also is check for a change in Range("No._Investments") hides the rows "166:173" and "179:186" when it equals 2 and when Partner is 2.

Red is top and Blue is bottom.


Private Sub Worksheet_Change(ByVal Target As Range)




Code: 
[COLOR=#ff0000]    If Not Intersect(Target, Range("No._Investments")) Is Nothing Then[/COLOR]
[COLOR=#ff0000]
[/COLOR]
[COLOR=#ff0000]
[/COLOR]
[COLOR=#ff0000]    If Target.Cells.CountLarge > 1 Then Exit Sub[/COLOR]
[COLOR=#ff0000]        Select Case Target.Value[/COLOR]
[COLOR=#ff0000]
[/COLOR]
[COLOR=#ff0000]
[/COLOR]
[COLOR=#ff0000]            Case "Please Select"[/COLOR]
[COLOR=#ff0000]                Range("26:136,139:149,163:292").EntireRow.Hidden = True[/COLOR]
[COLOR=#ff0000]        [/COLOR]
[COLOR=#ff0000]            Case 0[/COLOR]
[COLOR=#ff0000]                Range("26:136,139:149,163:292").EntireRow.Hidden = True[/COLOR]
[COLOR=#ff0000]        [/COLOR]
[COLOR=#ff0000]            Case 2[/COLOR]
[COLOR=#ff0000]                Range("26:136,139:149,163:292").EntireRow.Hidden = False[/COLOR]
[COLOR=#ff0000]                Range("50:136,142:149,189:292").EntireRow.Hidden = True[/COLOR]
[COLOR=#ff0000]        [/COLOR]
[COLOR=#ff0000]            Case 4[/COLOR]
[COLOR=#ff0000]                Range("26:136,139:149,163:292").EntireRow.Hidden = False[/COLOR]
[COLOR=#ff0000]                Range("72:136,144:149,215:292").EntireRow.Hidden = True[/COLOR]
[COLOR=#ff0000]          [/COLOR]
[COLOR=#ff0000]            Case 6[/COLOR]
[COLOR=#ff0000]                Range("26:136,139:149,163:292").EntireRow.Hidden = False[/COLOR]
[COLOR=#ff0000]                Range("94:136,146:149,241:292").EntireRow.Hidden = True[/COLOR]
[COLOR=#ff0000]        [/COLOR]
[COLOR=#ff0000]            Case 8[/COLOR]
[COLOR=#ff0000]                Range("26:136,139:149,163:292").EntireRow.Hidden = False[/COLOR]
[COLOR=#ff0000]                Range("116:136,148:149,267:292,").EntireRow.Hidden = True[/COLOR]
[COLOR=#ff0000]        [/COLOR]
[COLOR=#ff0000]            Case 10[/COLOR]
[COLOR=#ff0000]                Range("26:136,163:292,139:149").EntireRow.Hidden = False[/COLOR]
[COLOR=#ff0000]          [/COLOR]
[COLOR=#ff0000]        End Select[/COLOR]
[COLOR=#ff0000]        End If[/COLOR]
[COLOR=#ff0000]        [/COLOR]
[COLOR=#0000ff]Dim i As Range[/COLOR]
[COLOR=#0000ff]  Dim R As Range[/COLOR]
[COLOR=#0000ff]  Dim InvSel As Integer[/COLOR]
[COLOR=#0000ff]  Dim PartSel As Integer[/COLOR]
[COLOR=#0000ff]  Dim InvestRng As Range[/COLOR]
[COLOR=#0000ff]  Dim PartnerRng As Range[/COLOR]
[COLOR=#0000ff]  [/COLOR]
[COLOR=#0000ff]  Set InvestRng = Range("No._Investments")[/COLOR]
[COLOR=#0000ff]  Set PartnerRng = Range("No._Partners")[/COLOR]
[COLOR=#0000ff]  [/COLOR]
[COLOR=#0000ff]  Set i = Intersect(Target, InvestRng)[/COLOR]
[COLOR=#0000ff]  If Not i Is Nothing Then[/COLOR]
[COLOR=#0000ff]    InvSel = InvestRng.Value[/COLOR]
[COLOR=#0000ff]    PartSel = PartnerRng.Value[/COLOR]
[COLOR=#0000ff]    Select Case InvSel[/COLOR]
[COLOR=#0000ff]      Case 2[/COLOR]
[COLOR=#0000ff]        If PartSel = "Please Select" Then[/COLOR]
[COLOR=#0000ff]          Set R = Union(Range("165:173"), Range("178:186"))[/COLOR]
[COLOR=#0000ff]        ElseIf PartSel = 2 Then[/COLOR]
[COLOR=#0000ff]          Set R = Union(Range("166:173"), Range("179:186"))[/COLOR]
[COLOR=#0000ff]        ElseIf PartSel = 3 Then[/COLOR]
[COLOR=#0000ff]          Set R = Union(Range("167:173"), Range("180:186"))[/COLOR]
[COLOR=#0000ff]        ElseIf PartSel = 4 Then[/COLOR]
[COLOR=#0000ff]          Set R = Union(Range("168:173"), Range("181:186"))[/COLOR]
[COLOR=#0000ff]        ElseIf PartSel = 5 Then[/COLOR]
[COLOR=#0000ff]          Set R = Union(Range("169:173"), Range("182:186"))[/COLOR]
[COLOR=#0000ff]        ElseIf PartSel = 6 Then[/COLOR]
[COLOR=#0000ff]          Set R = Union(Range("170:173"), Range("183:186"))[/COLOR]
[COLOR=#0000ff]        ElseIf PartSel = 7 Then[/COLOR]
[COLOR=#0000ff]          Set R = Union(Range("171:173"), Range("184:186"))[/COLOR]
[COLOR=#0000ff]        ElseIf PartSel = 8 Then[/COLOR]
[COLOR=#0000ff]          Set R = Union(Range("172:173"), Range("185:186"))[/COLOR]
[COLOR=#0000ff]        ElseIf PartSel = 9 Then[/COLOR]
[COLOR=#0000ff]          Set R = Union(Range("173"), Range("186"))[/COLOR]
[COLOR=#0000ff]        End If[/COLOR]
[COLOR=#0000ff]      [/COLOR]
[COLOR=#0000ff]      Case 4[/COLOR]
[COLOR=#0000ff]        If PartSel = "Please Select" Then[/COLOR]
[COLOR=#0000ff]          Set R = Union(Range("165:173"), Range("178:186"), Range("191:199"), Range("204:212"))[/COLOR]
[COLOR=#0000ff]        ElseIf PartSel = 2 Then[/COLOR]
[COLOR=#0000ff]          Set R = Union(Range("166:173"), Range("179:186"), Range("192:199"), Range("205:212"))[/COLOR]
[COLOR=#0000ff]        ElseIf PartSel = 3 Then[/COLOR]
[COLOR=#0000ff]          Set R = Union(Range("167:173"), Range("180:186"), Range("193:199"), Range("206:212"))[/COLOR]
[COLOR=#0000ff]        ElseIf PartSel = 4 Then[/COLOR]
[COLOR=#0000ff]          Set R = Union(Range("168:173"), Range("181:186"), Range("194:199"), Range("207:212"))[/COLOR]
[COLOR=#0000ff]        ElseIf PartSel = 5 Then[/COLOR]
[COLOR=#0000ff]          [/COLOR]
[COLOR=#0000ff]          Set R = Union(Range("169:173"), Range("182:186"), Range("195:199"), Range("208:212"))[/COLOR]
[COLOR=#0000ff]        ElseIf PartSel = 6 Then[/COLOR]
[COLOR=#0000ff]          Set R = Union(Range("170:173"), Range("183:186"), Range("196:199"), Range("209:212"))[/COLOR]
[COLOR=#0000ff]        ElseIf PartSel = 7 Then[/COLOR]
[COLOR=#0000ff]          Set R = Union(Range("171:173"), Range("184:186"), Range("197:199"), Range("210:212"))[/COLOR]
[COLOR=#0000ff]        ElseIf PartSel = 8 Then[/COLOR]
[COLOR=#0000ff]          Set R = Union(Range("172:173"), Range("185:186"), Range("198:199"), Range("211:212"))[/COLOR]
[COLOR=#0000ff]        ElseIf PartSel = 9 Then[/COLOR]
[COLOR=#0000ff]          Set R = Union(Range("173"), Range("186"), Range("199"), Range("212"))[/COLOR]
[COLOR=#0000ff]        End If[/COLOR]
[COLOR=#0000ff]      Case 6[/COLOR]
[COLOR=#0000ff]        If PartSel = "Please Select" Then[/COLOR]
[COLOR=#0000ff]          Set R = Union(Range("165:173"), Range("178:186"), Range("191:199"), Range("204:212"), Range("217:225"), Range("230:238"))[/COLOR]
[COLOR=#0000ff]        ElseIf PartSel = 2 Then[/COLOR]
[COLOR=#0000ff]          Set R = Union(Range("166:173"), Range("179:186"), Range("192:199"), Range("205:212"), Range("218:225"), Range("231:238"))[/COLOR]
[COLOR=#0000ff]        ElseIf PartSel = 3 Then[/COLOR]
[COLOR=#0000ff]          Set R = Union(Range("167:173"), Range("180:186"), Range("193:199"), Range("206:212"), Range("219:225"), Range("232:238"))[/COLOR]
[COLOR=#0000ff]        ElseIf PartSel = 4 Then[/COLOR]
[COLOR=#0000ff]          Set R = Union(Range("168:173"), Range("181:186"), Range("194:199"), Range("207:212"), Range("220:225"), Range("233:238"))[/COLOR]
[COLOR=#0000ff]        ElseIf PartSel = 5 Then[/COLOR]
[COLOR=#0000ff]          Set R = Union(Range("169:173"), Range("182:186"), Range("195:199"), Range("208:212"), Range("221:225"), Range("234:238"))[/COLOR]
[COLOR=#0000ff]        ElseIf PartSel = 6 Then[/COLOR]
[COLOR=#0000ff]          Set R = Union(Range("170:173"), Range("183:186"), Range("196:199"), Range("209:212"), Range("222:225"), Range("235:238"))[/COLOR]
[COLOR=#0000ff]        ElseIf PartSel = 7 Then[/COLOR]
[COLOR=#0000ff]          Set R = Union(Range("171:173"), Range("184:186"), Range("197:199"), Range("210:212"), Range("223:225"), Range("236:238"))[/COLOR]
[COLOR=#0000ff]        ElseIf PartSel = 8 Then[/COLOR]
[COLOR=#0000ff]          Set R = Union(Range("172:173"), Range("185:186"), Range("198:199"), Range("211:212"), Range("224:225"), Range("237:238"))[/COLOR]
[COLOR=#0000ff]        ElseIf PartSel = 9 Then[/COLOR]
[COLOR=#0000ff]          Set R = Union(Range("173"), Range("186"), Range("199"), Range("212"), Range("225"), Range("238"))[/COLOR]
[COLOR=#0000ff]        End If[/COLOR]
[COLOR=#0000ff]      Case 8[/COLOR]
[COLOR=#0000ff]        If PartSel = "Please Select" Then[/COLOR]
[COLOR=#0000ff]          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"))[/COLOR]
[COLOR=#0000ff]        ElseIf PartSel = 2 Then[/COLOR]
[COLOR=#0000ff]          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"))[/COLOR]
[COLOR=#0000ff]        ElseIf PartSel = 3 Then[/COLOR]
[COLOR=#0000ff]          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"))[/COLOR]
[COLOR=#0000ff]        ElseIf PartSel = 4 Then[/COLOR]
[COLOR=#0000ff]          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"))[/COLOR]
[COLOR=#0000ff]        ElseIf PartSel = 5 Then[/COLOR]
[COLOR=#0000ff]          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"))[/COLOR]
[COLOR=#0000ff]        ElseIf PartSel = 6 Then[/COLOR]
[COLOR=#0000ff]          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"))[/COLOR]
[COLOR=#0000ff]        ElseIf PartSel = 7 Then[/COLOR]
[COLOR=#0000ff]          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"))[/COLOR]
[COLOR=#0000ff]        ElseIf PartSel = 8 Then[/COLOR]
[COLOR=#0000ff]          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"))[/COLOR]
[COLOR=#0000ff]        ElseIf PartSel = 9 Then[/COLOR]
[COLOR=#0000ff]          Set R = Union(Range("173"), Range("186"), Range("199"), Range("212"), Range("225"), Range("238"), Range("251"), Range("264"))[/COLOR]
[COLOR=#0000ff]        End If[/COLOR]
[COLOR=#0000ff]      Case 10[/COLOR]
[COLOR=#0000ff]        If PartSel = "Please Select" Then[/COLOR]
[COLOR=#0000ff]          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"))[/COLOR]
[COLOR=#0000ff]        ElseIf PartSel = 2 Then[/COLOR]
[COLOR=#0000ff]          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"))[/COLOR]
[COLOR=#0000ff]        ElseIf PartSel = 3 Then[/COLOR]
[COLOR=#0000ff]          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"))[/COLOR]
[COLOR=#0000ff]        ElseIf PartSel = 4 Then[/COLOR]
[COLOR=#0000ff]          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"))[/COLOR]
[COLOR=#0000ff]        ElseIf PartSel = 5 Then[/COLOR]
[COLOR=#0000ff]          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"))[/COLOR]
[COLOR=#0000ff]        ElseIf PartSel = 6 Then[/COLOR]
[COLOR=#0000ff]          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"))[/COLOR]
[COLOR=#0000ff]        ElseIf PartSel = 7 Then[/COLOR]
[COLOR=#0000ff]          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"))[/COLOR]
[COLOR=#0000ff]        ElseIf PartSel = 8 Then[/COLOR]
[COLOR=#0000ff]          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"))[/COLOR]
[COLOR=#0000ff]        ElseIf PartSel = 9 Then[/COLOR]
[COLOR=#0000ff]          Set R = Union(Range("173"), Range("186"), Range("199"), Range("212"), Range("225"), Range("238"), Range("251"), Range("264"), Range("277"), Range("290"))[/COLOR]
[COLOR=#0000ff]     End If[/COLOR]
[COLOR=#0000ff]    End Select[/COLOR]
[COLOR=#0000ff]      [/COLOR]
[COLOR=#0000ff]    If Not R Is Nothing Then R.EntireRow.Hidden = True[/COLOR]
[COLOR=#0000ff]  End If[/COLOR]
[COLOR=#0000ff]       [/COLOR]
          
If [h7] = "YES" Then
Sheets("K1a").Visible = True
Else
Sheets("K1a").Visible = False
End If
End Sub
 
Upvote 0

Excel Facts

Excel Can Read to You
Customize Quick Access Toolbar. From All Commands, add Speak Cells or Speak Cells on Enter to QAT. Select cells. Press Speak Cells.
Okay, so I've now taken out the top code as well because theoretically, if I can get this code to work, it can do everything.
I thought maybe an issue was that I was not addressing "Please Select" and "0" as an option on "No. of Investments", so I have now added these two cases.

It's not liking this at all and is returning - Compile Error: Argument not optional
and highlights "Union" on the first Set R

I've triple checked "Please Select" is spelt correctly and all seems fine here.

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("No._Partners")
  
  Set i = Intersect(Target, InvestRng)
  If Not i Is Nothing Then
    InvSel = InvestRng.Value
    PartSel = PartnerRng.Value
    Select Case InvSel
      Case "Please Select"
        If PartSel = "Please Select" Then
          Set R = Union(Range("163:292"))
        ElseIf PartSel = 2 Then
          Set R = Union(Range("163:292"))
        ElseIf PartSel = 3 Then
          Set R = Union(Range("163:292"))
        ElseIf PartSel = 4 Then
          Set R = Union(Range("163:292"))
        ElseIf PartSel = 5 Then
          Set R = Union(Range("163:292"))
        ElseIf PartSel = 6 Then
          Set R = Union(Range("163:292"))
        ElseIf PartSel = 7 Then
          Set R = Union(Range("163:292"))
        ElseIf PartSel = 8 Then
          Set R = Union(Range("163:292"))
        ElseIf PartSel = 9 Then
          Set R = Union(Range("163:292"))
        End If
     Case 0
        If PartSel = "Please Select" Then
          Set R = Union(Range("163:292"))
        ElseIf PartSel = 2 Then
          Set R = Union(Range("163:292"))
        ElseIf PartSel = 3 Then
          Set R = Union(Range("163:292"))
        ElseIf PartSel = 4 Then
          Set R = Union(Range("163:292"))
        ElseIf PartSel = 5 Then
          Set R = Union(Range("163:292"))
        ElseIf PartSel = 6 Then
          Set R = Union(Range("163:292"))
        ElseIf PartSel = 7 Then
          Set R = Union(Range("163:292"))
        ElseIf PartSel = 8 Then
          Set R = Union(Range("163:292"))
        ElseIf PartSel = 9 Then
          Set R = Union(Range("163:292"))
        End If
      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 Question
Hide Rows based on cell with drop down values
Replies
1
Views
1K
kevin9999
K
N
  • Question Question
Hiding/showing rows between two values
Replies
1
Views
405
Joe4
Joe4
B
  • Solved
Another VBA question to hide/unhide rows based on cell value in another sheet
2
Replies
12
Views
1K
kevin9999
K
E
  • Question Question
VBA Code to hide/unhide rows when a something is selected from drop down list.
Replies
4
Views
361
Essencex
E
J
  • Question Question
VBA Macro for hiding and unhiding rows is rearranging cells
2
Replies
14
Views
1K
dmt32
D

Forum statistics

Threads
1,223,227
Messages
6,170,849
Members
452,361
Latest member
d3ad3y3

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