Page 1 of 4 123 ... LastLast
Results 1 to 10 of 36

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

  1. #1
    Board Regular
    Join Date
    Jul 2018
    Location
    Auckland
    Posts
    303
    Post Thanks / Like
    Mentioned
    1 Post(s)
    Tagged
    0 Thread(s)

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

    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

  2. #2
    Board Regular
    Join Date
    Mar 2015
    Posts
    3,901
    Post Thanks / Like
    Mentioned
    72 Post(s)
    Tagged
    7 Thread(s)

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

    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

  3. #3
    Board Regular
    Join Date
    Jul 2018
    Location
    Auckland
    Posts
    303
    Post Thanks / Like
    Mentioned
    1 Post(s)
    Tagged
    0 Thread(s)

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

    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

  4. #4
    MrExcel MVP
    Moderator
    Fluff's Avatar
    Join Date
    Jun 2014
    Location
    Chippenham
    Posts
    28,177
    Post Thanks / Like
    Mentioned
    470 Post(s)
    Tagged
    47 Thread(s)

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

    Change both of these to Variant rather than Integer
    Code:
     Dim InvSel As Integer
     Dim PartSel As Integer
    Last edited by Fluff; Mar 11th, 2019 at 05:52 PM.
    - Posting Data try one of these tools
    - Posting guidelines, forum rules and terms of use
    - Read the FAQs

    Running Office 365 on Win 10

  5. #5
    Board Regular
    Join Date
    Jul 2018
    Location
    Auckland
    Posts
    303
    Post Thanks / Like
    Mentioned
    1 Post(s)
    Tagged
    0 Thread(s)

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

    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

  6. #6
    Board Regular
    Join Date
    Jul 2018
    Location
    Auckland
    Posts
    303
    Post Thanks / Like
    Mentioned
    1 Post(s)
    Tagged
    0 Thread(s)

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

    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

  7. #7
    Board Regular DanteAmor's Avatar
    Join Date
    Dec 2018
    Location
    México
    Posts
    6,238
    Post Thanks / Like
    Mentioned
    71 Post(s)
    Tagged
    14 Thread(s)

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

    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 by DanteAmor; Mar 11th, 2019 at 09:03 PM.
    Regards Dante Amor

  8. #8
    Board Regular
    Join Date
    Jul 2018
    Location
    Auckland
    Posts
    303
    Post Thanks / Like
    Mentioned
    1 Post(s)
    Tagged
    0 Thread(s)

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

    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..

  9. #9
    Board Regular DanteAmor's Avatar
    Join Date
    Dec 2018
    Location
    México
    Posts
    6,238
    Post Thanks / Like
    Mentioned
    71 Post(s)
    Tagged
    14 Thread(s)

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

    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.
    Regards Dante Amor

  10. #10
    Board Regular
    Join Date
    Jul 2018
    Location
    Auckland
    Posts
    303
    Post Thanks / Like
    Mentioned
    1 Post(s)
    Tagged
    0 Thread(s)

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

    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/520ltl0l3d...ents.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!

Some videos you may like

User Tag List

Tags for this Thread

Like this thread? Share it with others

Like this thread? Share it with others

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •