Hiding rows with multiple dropdown menu's

D

dguldemond

New Member
Joined
Sep 1, 2022
Messages
3
Office Version
  1. 365
Platform
  1. MacOS
Dear everyone,

I am new to this forum and quite a brookie in Excel. Currently I am trying to hide rows in excel using multiple drowdown menu's. My problem is that one dropdown menu is overruling the other dropdown menu while they should both be taken into account. Could anyone tell me what I am doing wrong or how to fix this code? Please find a screenshot of the output and the code I used below, as can be seen the 'units per property' is working, while the number of 'properties' is overruled by the number of units.

1662020370875.png


My code is below:
VBA Code: 
Private Sub Worksheet_Change(ByVal Target As Range)
      
   If Range("D7") = "-1   " Then
        Rows("1:12").EntireRow.Hidden = False
    Else
        Rows("13:81").EntireRow.Hidden = True
    End If
   
   If Range("D7") = "1" Then
        Rows("13:18").EntireRow.Hidden = False
    Else
        Rows("20:81").EntireRow.Hidden = True
    End If
   
    If Range("D7") = "2" Then
        Rows("13:25").EntireRow.Hidden = False
    Else
        Rows("26:81").EntireRow.Hidden = True
       
    End If
   
        If Range("D7") = "3" Then
        Rows("13:32").EntireRow.Hidden = False
    Else
        Rows("33:81").EntireRow.Hidden = True
             
    End If
   
        If Range("D7") = "4" Then
        Rows("13:39").EntireRow.Hidden = False
    Else
        Rows("40:81").EntireRow.Hidden = True
     End If
   
        If Range("D7") = "5" Then
        Rows("13:46").EntireRow.Hidden = False
    Else
        Rows("47:81").EntireRow.Hidden = True
        End If
   
        If Range("D7") = "6" Then
        Rows("13:53").EntireRow.Hidden = False
    Else
        Rows("54:81").EntireRow.Hidden = True
          End If
   
        If Range("D7") = "7" Then
        Rows("13:60").EntireRow.Hidden = False
    Else
        Rows("61:81").EntireRow.Hidden = True
          End If
   
        If Range("D7") = "8" Then
        Rows("13:67").EntireRow.Hidden = False
    Else
        Rows("68:81").EntireRow.Hidden = True
          End If
   
        If Range("D7") = "9" Then
        Rows("13:74").EntireRow.Hidden = False
    Else
        Rows("75:81").EntireRow.Hidden = True
          End If
   
        If Range("D7") = "10" Then
        Rows("13:81").EntireRow.Hidden = False
  End If
     
        If Range("D8") = "-1" Then
          Rows("1:12").EntireRow.Hidden = False
    Else
        Rows("13:81").EntireRow.Hidden = True
    End If
          
        If Range("D8") = "1" Then
        Rows("13").EntireRow.Hidden = False
         Rows("20").EntireRow.Hidden = False
         Rows("27").EntireRow.Hidden = False
         Rows("34").EntireRow.Hidden = False
         Rows("41").EntireRow.Hidden = False
         Rows("48").EntireRow.Hidden = False
         Rows("55").EntireRow.Hidden = False
         Rows("62").EntireRow.Hidden = False
         Rows("69").EntireRow.Hidden = False
         Rows("76").EntireRow.Hidden = False
                 Rows("19").EntireRow.Hidden = False
         Rows("26").EntireRow.Hidden = False
         Rows("33").EntireRow.Hidden = False
         Rows("40").EntireRow.Hidden = False
         Rows("47").EntireRow.Hidden = False
         Rows("54").EntireRow.Hidden = False
         Rows("61").EntireRow.Hidden = False
         Rows("68").EntireRow.Hidden = False
         Rows("75").EntireRow.Hidden = False
         Rows("82").EntireRow.Hidden = False
    Else
        Rows("14:18").EntireRow.Hidden = True
        Rows("21:25").EntireRow.Hidden = True
        Rows("28:32").EntireRow.Hidden = True
        Rows("35:39").EntireRow.Hidden = True
        Rows("42:46").EntireRow.Hidden = True
        Rows("49:53").EntireRow.Hidden = True
        Rows("56:60").EntireRow.Hidden = True
        Rows("63:67").EntireRow.Hidden = True
        Rows("70:74").EntireRow.Hidden = True
        Rows("77:81").EntireRow.Hidden = True
          End If

        If Range("D8") = "2" Then
        Rows("13:14").EntireRow.Hidden = False
         Rows("20:21").EntireRow.Hidden = False
         Rows("27:28").EntireRow.Hidden = False
         Rows("34:35").EntireRow.Hidden = False
         Rows("41:42").EntireRow.Hidden = False
         Rows("48:49").EntireRow.Hidden = False
         Rows("55:56").EntireRow.Hidden = False
         Rows("62:63").EntireRow.Hidden = False
         Rows("69:70").EntireRow.Hidden = False
         Rows("76:77").EntireRow.Hidden = False
                          Rows("19").EntireRow.Hidden = False
         Rows("26").EntireRow.Hidden = False
         Rows("33").EntireRow.Hidden = False
         Rows("40").EntireRow.Hidden = False
         Rows("47").EntireRow.Hidden = False
         Rows("54").EntireRow.Hidden = False
         Rows("61").EntireRow.Hidden = False
         Rows("68").EntireRow.Hidden = False
         Rows("75").EntireRow.Hidden = False
         Rows("82").EntireRow.Hidden = False
    Else
        Rows("15:18").EntireRow.Hidden = True
        Rows("22:25").EntireRow.Hidden = True
        Rows("29:32").EntireRow.Hidden = True
        Rows("36:39").EntireRow.Hidden = True
        Rows("43:46").EntireRow.Hidden = True
        Rows("50:53").EntireRow.Hidden = True
        Rows("57:60").EntireRow.Hidden = True
        Rows("64:67").EntireRow.Hidden = True
        Rows("71:74").EntireRow.Hidden = True
        Rows("78:81").EntireRow.Hidden = True
          End If

    If Range("D8") = "3" Then
        Rows("13:15").EntireRow.Hidden = False
         Rows("20:22").EntireRow.Hidden = False
         Rows("27:29").EntireRow.Hidden = False
         Rows("34:36").EntireRow.Hidden = False
         Rows("41:43").EntireRow.Hidden = False
         Rows("48:50").EntireRow.Hidden = False
         Rows("55:57").EntireRow.Hidden = False
         Rows("62:64").EntireRow.Hidden = False
         Rows("69:71").EntireRow.Hidden = False
         Rows("76:78").EntireRow.Hidden = False
         Rows("19").EntireRow.Hidden = False
         Rows("26").EntireRow.Hidden = False
         Rows("33").EntireRow.Hidden = False
         Rows("40").EntireRow.Hidden = False
         Rows("47").EntireRow.Hidden = False
         Rows("54").EntireRow.Hidden = False
         Rows("61").EntireRow.Hidden = False
         Rows("68").EntireRow.Hidden = False
         Rows("75").EntireRow.Hidden = False
         Rows("82").EntireRow.Hidden = False
    Else
        Rows("16:18").EntireRow.Hidden = True
        Rows("23:25").EntireRow.Hidden = True
        Rows("30:32").EntireRow.Hidden = True
        Rows("37:39").EntireRow.Hidden = True
        Rows("44:46").EntireRow.Hidden = True
        Rows("51:53").EntireRow.Hidden = True
        Rows("58:60").EntireRow.Hidden = True
        Rows("65:67").EntireRow.Hidden = True
        Rows("72:74").EntireRow.Hidden = True
        Rows("79:81").EntireRow.Hidden = True
          End If

    If Range("D8") = "4" Then
        Rows("13:16").EntireRow.Hidden = False
         Rows("20:23").EntireRow.Hidden = False
         Rows("27:30").EntireRow.Hidden = False
         Rows("34:37").EntireRow.Hidden = False
         Rows("41:44").EntireRow.Hidden = False
         Rows("48:51").EntireRow.Hidden = False
         Rows("55:58").EntireRow.Hidden = False
         Rows("62:65").EntireRow.Hidden = False
         Rows("69:72").EntireRow.Hidden = False
         Rows("76:79").EntireRow.Hidden = False
                          Rows("19").EntireRow.Hidden = False
         Rows("26").EntireRow.Hidden = False
         Rows("33").EntireRow.Hidden = False
         Rows("40").EntireRow.Hidden = False
         Rows("47").EntireRow.Hidden = False
         Rows("54").EntireRow.Hidden = False
         Rows("61").EntireRow.Hidden = False
         Rows("68").EntireRow.Hidden = False
         Rows("75").EntireRow.Hidden = False
         Rows("82").EntireRow.Hidden = False
    Else
        Rows("17:18").EntireRow.Hidden = True
        Rows("24:25").EntireRow.Hidden = True
        Rows("31:32").EntireRow.Hidden = True
        Rows("38:39").EntireRow.Hidden = True
        Rows("45:46").EntireRow.Hidden = True
        Rows("52:53").EntireRow.Hidden = True
        Rows("59:60").EntireRow.Hidden = True
        Rows("66:67").EntireRow.Hidden = True
        Rows("73:74").EntireRow.Hidden = True
        Rows("80:81").EntireRow.Hidden = True
          End If

    If Range("D8") = "5" Then
        Rows("13:17").EntireRow.Hidden = False
         Rows("20:24").EntireRow.Hidden = False
         Rows("27:31").EntireRow.Hidden = False
         Rows("34:38").EntireRow.Hidden = False
         Rows("41:45").EntireRow.Hidden = False
         Rows("48:52").EntireRow.Hidden = False
         Rows("55:59").EntireRow.Hidden = False
         Rows("62:66").EntireRow.Hidden = False
         Rows("69:73").EntireRow.Hidden = False
         Rows("76:80").EntireRow.Hidden = False
                          Rows("19").EntireRow.Hidden = False
         Rows("26").EntireRow.Hidden = False
         Rows("33").EntireRow.Hidden = False
         Rows("40").EntireRow.Hidden = False
         Rows("47").EntireRow.Hidden = False
         Rows("54").EntireRow.Hidden = False
         Rows("61").EntireRow.Hidden = False
         Rows("68").EntireRow.Hidden = False
         Rows("75").EntireRow.Hidden = False
         Rows("82").EntireRow.Hidden = False
    Else
        Rows("18").EntireRow.Hidden = True
        Rows("25").EntireRow.Hidden = True
        Rows("32").EntireRow.Hidden = True
        Rows("39").EntireRow.Hidden = True
        Rows("46").EntireRow.Hidden = True
        Rows("53").EntireRow.Hidden = True
        Rows("60").EntireRow.Hidden = True
        Rows("67").EntireRow.Hidden = True
        Rows("74").EntireRow.Hidden = True
        Rows("81").EntireRow.Hidden = True
          End If

    If Range("D8") = "6" Then
        Rows("13:81").EntireRow.Hidden = False
       
  
  End If
    
    End Sub

Let me know if you know how to fix me problem? I have been trying a lot.

Kind regards,

DG
 

Excel Facts

Difference between two dates
Secret function! Use =DATEDIF(A2,B2,"Y")&" years"&=DATEDIF(A2,B2,"YM")&" months"&=DATEDIF(A2,B2,"MD")&" days"
Sort by date Sort by votes
Try this & let me know how you go. I won't get back to it tonight, but I will respond tomorrow.
EDITED to remove the "-1" case for cell D8 which was overriding the cell D7 value & code simplified.

VBA Code: 
Private Sub Worksheet_Change(ByVal Target As Range)
    If Target.Cells.CountLarge = 1 And Not Intersect(Range("D7:D8"), Target) Is Nothing Then
        Dim i As Long, j As Long, a As String, b As String
        Range("13:81").EntireRow.Hidden = 0
        a = Range("D7"): b = Range("D8")
        
        Select Case a
            Case Is = -1
                Range("13:81").EntireRow.Hidden = 1
            Case Is = 1
                Range("20:81").EntireRow.Hidden = 1
            Case Is = 2
                Range("26:81").EntireRow.Hidden = 1
            Case Is = 3
                Range("33:81").EntireRow.Hidden = 1
            Case Is = 4
                Range("40:81").EntireRow.Hidden = 1
            Case Is = 5
                Range("47:81").EntireRow.Hidden = 1
            Case Is = 6
                Range("54:81").EntireRow.Hidden = 1
            Case Is = 7
                Range("61:81").EntireRow.Hidden = 1
            Case Is = 8
                Range("68:81").EntireRow.Hidden = 1
            Case Is = 9
                Range("75:81").EntireRow.Hidden = 1
        End Select
        
        If b <> "" Then
            j = 13
            For i = 1 To a
                Rows(j).Offset(b).Resize(7 - b).EntireRow.Hidden = 1
                j = j + 7
            Next i
        End If
    End If
End Sub
 
Last edited:
Upvote 0
Holy ****, this worked perfectly, thank you so much!!! Just one more question: what do I have to change in the code to get the blanco rows inbetween properties back? (row 19, 26, 33, etc.)
 
Upvote 0
Try this (EDITED)

VBA Code: 
Private Sub Worksheet_Change(ByVal Target As Range)
    If Target.Cells.CountLarge = 1 And Not Intersect(Range("D7:D8"), Target) Is Nothing Then
        Application.EnableEvents = False
        Dim i As Long, j As Long, a As String, b As String
        Range("13:81").EntireRow.Hidden = 0
        a = Range("D7"): b = Range("D8")
       
        If b <> "" AND a <> "" Then
            j = 13
            For i = 1 To a
                Rows(j).Offset(b).Resize(6 - b).EntireRow.Hidden = 1
                j = j + 7
            Next i
        End If
       
        If a <> "" Then
            Select Case a
                Case Is = -1
                    Range("13:81").EntireRow.Hidden = 1
                Case Is = 1
                    Range("20:81").EntireRow.Hidden = 1
                Case Is = 2
                    Range("26:81").EntireRow.Hidden = 1
                Case Is = 3
                    Range("33:81").EntireRow.Hidden = 1
                Case Is = 4
                    Range("40:81").EntireRow.Hidden = 1
                Case Is = 5
                    Range("47:81").EntireRow.Hidden = 1
                Case Is = 6
                    Range("54:81").EntireRow.Hidden = 1
                Case Is = 7
                    Range("61:81").EntireRow.Hidden = 1
                Case Is = 8
                    Range("68:81").EntireRow.Hidden = 1
                Case Is = 9
                    Range("75:81").EntireRow.Hidden = 1
            End Select
        End If
       
        Application.EnableEvents = True
    End If
End Sub
 
Last edited:
Upvote 0
Revised code - ignore post #4

VBA Code: 
Private Sub Worksheet_Change(ByVal Target As Range)
    If Target.Cells.CountLarge = 1 And Not Intersect(Range("D7:D8"), Target) Is Nothing Then
        Application.EnableEvents = False
        Dim i As Long, j As Long, a As String, b As String
        Range("13:81").EntireRow.Hidden = 0
        a = Range("D7"): b = Range("D8")
        
        If b <> "" And a <> "" Then
            If b = 6 Then
                Range("13:81").EntireRow.Hidden = 0
            Else
            j = 13
            For i = 1 To a
                Rows(j).Offset(b).Resize(6 - b).EntireRow.Hidden = 1
                j = j + 7
            Next i
            End If
        End If
        
        If a <> "" Then
            Select Case a
                Case Is = -1
                    Range("13:81").EntireRow.Hidden = 1
                Case Is = 1
                    Range("20:81").EntireRow.Hidden = 1
                Case Is = 2
                    Range("26:81").EntireRow.Hidden = 1
                Case Is = 3
                    Range("33:81").EntireRow.Hidden = 1
                Case Is = 4
                    Range("40:81").EntireRow.Hidden = 1
                Case Is = 5
                    Range("47:81").EntireRow.Hidden = 1
                Case Is = 6
                    Range("54:81").EntireRow.Hidden = 1
                Case Is = 7
                    Range("61:81").EntireRow.Hidden = 1
                Case Is = 8
                    Range("68:81").EntireRow.Hidden = 1
                Case Is = 9
                    Range("75:81").EntireRow.Hidden = 1
            End Select
        End If
        
        Application.EnableEvents = True
    End If
End Sub
 
Upvote 0
Solution
Thanks a lot Kevin. This really helped me. Interesting how the VBA coding works, really need to get into this, I think it's a very interesting tool to make excel-files way sicker.
 
Upvote 0
dguldemond said:
Thanks a lot Kevin. This really helped me. Interesting how the VBA coding works, really need to get into this, I think it's a very interesting tool to make excel-files way sicker.
Click to expand...
Glad I could help, and thanks for the feedback 😊
 
Upvote 0
You must log in or register to reply here.

Similar threads

imback2nite
  • Solved
VBA help.
2
Replies
10
Views
365
Peter_SSs
Peter_SSs
J
  • Question
VBA Macro for hiding and unhiding rows is rearranging cells
2
Replies
14
Views
941
dmt32
D
lonlyboy
  • Question
Hides Rows based on cell value on multiple worksheets
Replies
3
Views
244
Peter_SSs
Peter_SSs
A
  • Solved
how to protect sheets vba code
2
Replies
19
Views
798
Scuba94
S
B
  • Solved
Another VBA question to hide/unhide rows based on cell value in another sheet
2
Replies
12
Views
441
kevin9999
K

Forum statistics

Threads
1,215,045
Messages
6,122,830
Members
449,096
Latest member
Erald

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