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

Whats the difference between CONCAT and CONCATENATE?
The newer CONCAT function can reference a range of cells. =CONCATENATE(A1,A2,A3,A4,A5) becomes =CONCAT(A1:A5)
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
803
Peter_SSs
Peter_SSs
N
  • Question Question
Hiding/showing rows between two values
Replies
1
Views
479
Joe4
Joe4
J
  • Question Question
VBA Macro for hiding and unhiding rows is rearranging cells
2
Replies
14
Views
1K
dmt32
D
O
  • Question Question
Somewhat basic UDFs work except that they return #VALUE! error after hiding rows with VBA.
Replies
2
Views
129
OaklandJim
O
lonlyboy
  • Question Question
Hides Rows based on cell value on multiple worksheets
Replies
3
Views
392
Peter_SSs
Peter_SSs

Forum statistics

Threads
1,224,818
Messages
6,181,152
Members
453,021
Latest member
Justyna P

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