VBA Code Not Working / Error - Hide rows depending on 2x drop down lists

tlc53

tlc53

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

Before I go back to the drawing board, can anyone see whether they can get this VBA code to work?

Basically, I have two drop down lists. One is "No. of Investments" and the other is "No. of Partners". I would like certain rows to hide, depending on the result of both drop down lists.

Each case below shows the change in "No. of Investments" and then looks at the drop down list of "No. of Partners" and hides the rows accordingly (well, it's supposed to atleast).

Often, drop down list "No. of Partners" will not be engaged and will remain on "Please Select", so the first line of code will be used depending on the "No. of Investments" selected.

Currently the error being returned is "Compile Error - Argument no Optional" and it highlights the word "Union" on the first "Set R" line.

Drop down lists look like this;
No._Investments = Please Select,0,2,4,6,8,10
No._Partners = Please Select,2,3,4,5,6,7,8,9,10

Thanks for looking.


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
 

Excel Facts

What do {} around a formula in the formula bar mean?
{Formula} means the formula was entered using Ctrl+Shift+Enter signifying an old-style array formula.
Sort by date Sort by votes
Union requires TWO or more ranges, Range("163:292") is ONE range
Instead of
Code: 
    Set R = Union(Range("163:292"))
Use
Code: 
    Set R = Range("163:292")




For info
Code: 
        If PartSel = "Please Select" Then
          Set R = Range("163:292")
        ElseIf PartSel = 2 Then
          Set R = Range("163:292")
        ElseIf PartSel = 3 Then
          Set R = Range("163:292")
        ElseIf PartSel = 4 Then
          Set R = Range("163:292")
        ElseIf PartSel = 5 Then
          Set R = Range("163:292")
        ElseIf PartSel = 6 Then
          Set R = Range("163:292")
        ElseIf PartSel = 7 Then
          Set R = Range("163:292")
        ElseIf PartSel = 8 Then
          Set R = Range("163:292")
        ElseIf PartSel = 9 Then
          Set R = Range("163:292")
        End If

is the same as
Code: 
    Select Case PartSel
        Case "Please Select", 2, 3, 4, 5, 6, 7, 8, 9
            Set R = Range("163:292")
     End Select
 
Upvote 0
Thanks for that! I've now amended the code with your shorter version.
However, now it's returning another error, "Run-time error '13: Type mismatch" and it's highlighting "PartSel = PartnerRng.Value"

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"
        Select Case PartSel
        Case "Please Select", 2, 3, 4, 5, 6, 7, 8, 9
            Set R = Range("163:292")
        End Select
     Case 0
        Select Case PartSel
        Case "Please Select", 2, 3, 4, 5, 6, 7, 8, 9
            Set R = Range("163:292")
        End Select
     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
Change both of these to Variant rather than Integer
Code: 
 Dim InvSel As Integer
 Dim PartSel As Integer
 
Last edited:
Upvote 0
Thanks Fluff! It's not returning any errors now! :)
Only problem is, it doesn't seem to be doing anything. I change the No. of Partners and No. of Investments but nothing happens. It doesn't hide any rows. I made sure all the rows were unhidden first before making a selection but that hasn't made a difference. Any other suggestions??



Code: 
Private Sub Worksheet_Change(ByVal Target As Range)


Dim i As Range
  Dim R As Range
  Dim InvSel As Variant
  Dim PartSel As Variant
  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"
        Select Case PartSel
        Case "Please Select", 2, 3, 4, 5, 6, 7, 8, 9
            Set R = Range("163:292")
        End Select
     Case 0
        Select Case PartSel
        Case "Please Select", 2, 3, 4, 5, 6, 7, 8, 9
            Set R = Range("163:292")
        End Select
     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
Usually when hiding/unhiding rows based on a drop down (this is my first time using the result of 2x drop downs though) I use code similar to below to hide/unhide the rows. Should I be putting something like this within the code?


Code: 
  Case 0
        Range("11:142").EntireRow.Hidden = True
    
    Case 2
        Range("11:142").EntireRow.Hidden = False
        Range("22:29,39:142").EntireRow.Hidden = True
        
    Case 4
        Range("11:142").EntireRow.Hidden = False
        Range("22:29,48:55,65:142").EntireRow.Hidden = True
    
    Case 6
        Range("11:142").EntireRow.Hidden = False
        Range("22:29,48:55,74:81,91:142").EntireRow.Hidden = True
 
Upvote 0
Change your code to the next one. Try and tell me.

Code: 
Private Sub Worksheet_Change(ByVal Target As Range)


    Dim InvSel As Variant, PartSel As Variant, part1 As Variant, part2 As Variant
    Dim InvestRng As Range, PartnerRng As Range, r As Range, twoRng As Range
    Dim waum As Long, ini1 As Long, fin1 As Long, k As Long
    
    If Target.Count > 2 Then Exit Sub
    
    Set InvestRng = Range("No._Investments")
    Set PartnerRng = Range("No._Partners")
    Set twoRng = Union(InvestRng, PartnerRng)
    If Not Intersect(Target, twoRng) Is Nothing Then
    
        Rows("163:292").EntireRow.Hidden = False
        part1 = Array(163, , 165, , 191, , 217, , 243, , 269)
        part2 = Array(292, , 173, , 199, , 225, , 251, , 277)
        
        InvSel = IIf(InvestRng.Value = "Please Select", 0, InvestRng.Value)
        PartSel = PartnerRng.Value
        If InvSel = 0 Then
            Set r = Range(part1(0) & ":" & part2(0))
        Else
            For k = 2 To InvSel Step 2
                If PartSel = "Please Select" Then waum = 0 Else waum = PartSel - 1
                ini1 = part1(k) + waum
                fin1 = part2(k)
                If r Is Nothing Then
                    Set r = Union(Range(ini1 & ":" & fin1), Range(ini1 + 13 & ":" & fin1 + 13))
                Else
                    Set r = Union(r, Range(ini1 & ":" & fin1), Range(ini1 + 13 & ":" & fin1 + 13))
                End If
            Next
        End If
        
        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
 
Last edited:
Upvote 0
Hi, thanks for your code!
I tried putting it in. It doesn't return any errors but it doesn't appear to be doing anything either..
 
Upvote 0
How do you have the data on your sheet? You should have a cell named as No._Investments and another cell named as No._Partners
You could upload a copy of your file to a free site such as www.box.com or www.dropbox.com. Once you do that, mark it for 'Sharing' and you will be given a link to the file that you can post here. If the workbook contains confidential information, you could replace it with generic data.
 
Upvote 0
You know what, it is working!!! Thank you :)
However, your code is pretty much impossible for me to follow and I was hoping to add more rows to hide.
For example, lines on the Partner Shares at the top, depending on how many partners selected, plus hide Share Calculation boxes that aren't being used (for example, if 2 investments, hide calcs for No. 3-10, rows 50-135), plus rows in the Reconciliation (there's a reconciliation line for each investment), plus hide further rows on the journal side (eg. 2 investments needs to hide further rows 189-292).

Your code is correctly hiding the rows in the journal section, depending on the number of partners.

Here's a link of the sheet so you can see what it is I'm trying to achieve.. https://www.dropbox.com/s/520ltl0l3d3dloh/K1 re Investments.xlsm?dl=0

It's basically for presentation so that areas that are not required, are hidden from sight. Please let me know if I'm asking too much!
 
Upvote 0
You must log in or register to reply here.

Similar threads

L
  • Solved
VBA Find Cell from Date listed, in Columns, Return cell to highlight in color
Replies
7
Views
497
mumps
mumps
C
  • Question Question
Excel Formula to keep records of drawn cards
Replies
3
Views
280
Cquake
C
M
  • Solved
HELP---- Count Cells in a row based on Month and Unique cell contents.
2
Replies
16
Views
605
Peter_SSs
Peter_SSs
A
  • Question Question
Data Selection Based on Closest Value [20% Tolerance]
Replies
1
Views
204
StephenCrump
S
J
  • Solved
how to random all numbers
Replies
7
Views
270
Fluff
Fluff

Forum statistics

Threads
1,223,894
Messages
6,175,254
Members
452,624
Latest member
gregg777

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