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

tlc53

Active Member
Joined
Jul 26, 2018
Messages
365
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
 

Some videos you may like

Excel Facts

When they said...
When they said you are going to "Excel at life", they meant you "will be doing Excel your whole life".

Yongle

Well-known Member
Joined
Mar 11, 2015
Messages
6,710
Office Version
  1. 365
Platform
  1. Windows
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
 

tlc53

Active Member
Joined
Jul 26, 2018
Messages
365
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
 

Fluff

MrExcel MVP, Moderator
Joined
Jun 12, 2014
Messages
46,133
Office Version
  1. 365
Platform
  1. Windows
Change both of these to Variant rather than Integer
Code:
 Dim InvSel As Integer
 Dim PartSel As Integer
 
Last edited:

tlc53

Active Member
Joined
Jul 26, 2018
Messages
365

ADVERTISEMENT

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
 

tlc53

Active Member
Joined
Jul 26, 2018
Messages
365
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
 

DanteAmor

Well-known Member
Joined
Dec 3, 2018
Messages
12,593
Office Version
  1. 2007
Platform
  1. Windows

ADVERTISEMENT

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:

tlc53

Active Member
Joined
Jul 26, 2018
Messages
365
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..
 

DanteAmor

Well-known Member
Joined
Dec 3, 2018
Messages
12,593
Office Version
  1. 2007
Platform
  1. Windows
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.
 

tlc53

Active Member
Joined
Jul 26, 2018
Messages
365
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!
 

Watch MrExcel Video

Forum statistics

Threads
1,108,702
Messages
5,524,410
Members
409,576
Latest member
az168

This Week's Hot Topics

Top