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
 
Sort by date Sort by votes
You can keep the code and add the lines of code at the end to hide other rows.

I'll explain, the rows you want to hide have a behavior:
If InvestRng = 0 they always hide the rows from 163 to 292 (this is in the 0 position of the part1 array)

Code: 
If InvSel = 0 Then
Set r = Range (part1 (0) & ":" & part2 (0))


If InvestRng = 2, 4, 6, 8 or 10, then:

Code: 
        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

In the arrays part1 and part2 I have the starting and ending positions of the range:

Code: 
        part1 = Array(163, , 165, , 191, , 217, , 243, , 269)
        part2 = Array(292, , 173, , 199, , 225, , 251, , 277)


When you select 2, 4, 6, 8 or 10 from InvestRng, the macro takes the value of the position of the array1: 2 (165), 4 (191), 6 (217), 8 (243) or 10 (269) for form the beginning of the range.
And it also takes the value of the position of the array2 to form the end of the range.

For example: if in InvestRng you select 2 and in PartnerRng you select 3, then the macro does this:

Code: 
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


With values

Code: 
    If PartSel = "Please Select" Then waum = 0 Else waum = [COLOR=#0000ff]3 - 1[/COLOR]
                ini1 = part1([COLOR=#0000ff]2[/COLOR]) + waum
                fin1 = part2([COLOR=#0000ff]2[/COLOR])
                If r Is Nothing Then
                    Set r = Union (Range (ini1 & ":" & fin1), Range (ini1 + 13 & ":" & fin1 + 13))

Substituting
Code: 
    waum = 2
                ini1 = 165 + waum
                fin1 = 173
                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


Substituting
Code: 
   waum = 2
                ini1 = 165 + 2
                fin1 = 173
                If r Is Nothing Then
                    Set r = Union (Range (ini1 & ":" & 173), Range (ini1 + 13 & ":" & 173+ 13))


Substituting
Code: 
   waum = 2
                ini1 = 167
                fin1 = 173
                If r Is Nothing Then
[B][COLOR=#0000ff]                    Set r = Union (Range("167:173"), Range("180: 186"))[/COLOR][/B]

With your code:
Code: 
     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
[B][COLOR=#0000ff]          Set R = Union(Range("167:173"), Range("180:186"))[/COLOR][/B]
 
Upvote 0

Excel Facts

Create a Pivot Table on a Map
If your data has zip codes, postal codes, or city names, select the data and use Insert, 3D Map. (Found to right of chart icons).
Thanks so much for taking the time to write this code AND explain it to me!
It now works perfectly!! :) :) :)
 
Upvote 0
Hi Dante,
This code has been working great for me, however I have noticed one little problem.
It works great for number of partners 1 through to 9, however, it's not working correctly on 10 partners. I can kind of see why but not sure how you fix it. Say number of partners 10, number of investments 2, all rows should be unhidden from 163:175 and 176:188. Instead it is hiding 173:174 and 186:187. It hides those 2 corresponding rows when the investment number is increased. Is there an easy fix for this please? Thank you!

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
 
Upvote 0
This is my test rows 173 and 186 are hidden, rows 174 and 187 according to the rules should be visible

<b>cha1</b><br /><br /><table border="1" cellspacing="0" style="font-family:Calibri,Arial; font-size:11pt; background-color:#ffffff; "> <colgroup><col style="font-weight:bold; width:30px; " /><col style="width:80px;" /><col style="width:80px;" /><col style="width:89px;" /></colgroup><tr style="background-color:#cacaca; text-align:center; font-weight:bold; font-size:8pt; "><td > </td><td >A</td><td >B</td><td >C</td></tr><tr style="height:18px ;" ><td style="font-size:8pt; background-color:#cacaca; text-align:center; " >1</td><td style="text-align:right; ">1</td><td >inv</td><td >part</td></tr><tr style="height:18px ;" ><td style="font-size:8pt; background-color:#cacaca; text-align:center; " >2</td><td style="text-align:right; ">2</td><td style="background-color:#92d050; text-align:right; ">10</td><td style="background-color:#92d050; text-align:right; ">2</td></tr><tr style="height:18px ;" ><td style="font-size:8pt; background-color:#cacaca; text-align:center; " >3</td><td style="text-align:right; ">3</td><td > </td><td > </td></tr><tr style="height:18px ;" ><td style="font-size:8pt; background-color:#cacaca; text-align:center; " >4</td><td style="text-align:right; ">4</td><td > </td><td > </td></tr><tr style="height:18px ;" ><td style="font-size:8pt; background-color:#cacaca; text-align:center; " >5</td><td style="text-align:right; ">5</td><td > </td><td > </td></tr><tr style="height:18px ;" ><td style="font-size:8pt; background-color:#cacaca; text-align:center; " >6</td><td style="text-align:right; ">6</td><td > </td><td > </td></tr><tr style="height:18px ;" ><td style="font-size:8pt; background-color:#cacaca; text-align:center; " >7</td><td style="text-align:right; ">7</td><td > </td><td > </td></tr><tr style="height:18px ;" ><td style="font-size:8pt; background-color:#cacaca; text-align:center; " >8</td><td style="text-align:right; ">8</td><td > </td><td > </td></tr><tr style="height:18px ;" ><td style="font-size:8pt; background-color:#cacaca; text-align:center; " >9</td><td style="text-align:right; ">9</td><td > </td><td > </td></tr><tr style="height:18px ;" ><td style="font-size:8pt; background-color:#cacaca; text-align:center; " >10</td><td style="text-align:right; ">10</td><td > </td><td > </td></tr><tr style="height:18px ;" ><td style="font-size:8pt; background-color:#cacaca; text-align:center; " >11</td><td style="text-align:right; ">11</td><td > </td><td > </td></tr><tr style="height:18px ;" ><td style="font-size:8pt; background-color:#cacaca; text-align:center; " >12</td><td style="text-align:right; ">12</td><td > </td><td > </td></tr><tr style="height:18px ;" ><td style="font-size:8pt; background-color:#cacaca; text-align:center; " >13</td><td style="text-align:right; ">13</td><td > </td><td > </td></tr><tr style="height:18px ;" ><td style="font-size:8pt; background-color:#cacaca; text-align:center; " >162</td><td style="text-align:right; ">162</td><td > </td><td > </td></tr><tr style="height:18px ;" ><td style="font-size:8pt; background-color:#cacaca; text-align:center; " >163</td><td style="text-align:right; ">163</td><td > </td><td > </td></tr><tr style="height:18px ;" ><td style="font-size:8pt; background-color:#cacaca; text-align:center; " >164</td><td style="text-align:right; ">164</td><td > </td><td > </td></tr><tr style="height:18px ;" ><td style="font-size:8pt; background-color:#cacaca; text-align:center; " >165</td><td style="text-align:right; ">165</td><td > </td><td > </td></tr><tr style="height:18px ;" ><td style="font-size:8pt; background-color:#cacaca; text-align:center; " >174</td><td style="text-align:right; ">174</td><td > </td><td > </td></tr><tr style="height:18px ;" ><td style="font-size:8pt; background-color:#cacaca; text-align:center; " >175</td><td style="text-align:right; ">175</td><td > </td><td > </td></tr><tr style="height:18px ;" ><td style="font-size:8pt; background-color:#cacaca; text-align:center; " >176</td><td style="text-align:right; ">176</td><td > </td><td > </td></tr><tr style="height:18px ;" ><td style="font-size:8pt; background-color:#cacaca; text-align:center; " >177</td><td style="text-align:right; ">177</td><td > </td><td > </td></tr><tr style="height:18px ;" ><td style="font-size:8pt; background-color:#cacaca; text-align:center; " >178</td><td style="text-align:right; ">178</td><td > </td><td > </td></tr><tr style="height:18px ;" ><td style="font-size:8pt; background-color:#cacaca; text-align:center; " >187</td><td style="text-align:right; ">187</td><td > </td><td > </td></tr></table> <br /><br />
 
Upvote 0
Yes, that is correct. No problem with Inv = 10 Part = 2
It's when, for example, Inv = 2 Part = 10

It's not liking Partner 10, Partner 9 and below works perfectly.
 
Upvote 0
I think you did not have 10 in your original code, but I check it
 
Upvote 0
Try this

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 fin1 > ini1 Then
                    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
                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
 
Upvote 0
Thank you Dante! I hadn't realised you had replied so I've only seen this today.
10 partners now seems to be working fine but 9 partners, is now acting like 10 and not hiding the 1x row.
I have amended the code further (well tried to!) so that now it includes all investment numbers from 1 to 10, not just even numbers. It's kind of working but when an uneven number is selected under investments, it shows the next even setting (eg. 7 shows 8 ).
Also, regardless of selection it's hiding rows 295:304. I'm not sure why as this is outside of the range.
Sorry to bother you again but are you able to look at this code again please?
Thank you!

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, 178, 191, 204, 217, 230, 243, 256, 269, 282)
        part2 = Array(292, 173, 186, 199, 212, 225, 238, 251, 264, 277, 290)
        
        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 = 1 To InvSel Step 1
                If PartSel = "Please Select" Then waum = 0 Else waum = PartSel - 1
                ini1 = part1(k) + waum
                fin1 = part2(k)
                If fin1 > ini1 Then
                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
                End If
            Next
        End If
        
        If Not r Is Nothing Then r.EntireRow.Hidden = True
    End If
 
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
281
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,907
Messages
6,175,301
Members
452,633
Latest member
DougMo

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