VBA drop down menue to hide and unhide rows

[josh_oneill]

Dear all,

I have searched online for a solution but was unable to find exactly what I need.

I have a spreadsheet to include products in a particular section. I have designated a section of 10 rows for the products I wish to enter. I have a drop down menu/ list with the numbers 0-10. I would like the drop down to hide or un-hide the the same number of rows that is selected.
i.e. I select the number 3, only the first 3 rows should appear. If I then select 8 the first 8 rows should appear and so on. I need this to also hide rows if I had 8 products and now decide I only needed 4 products, only the first 4 rows should be visible.

My drop down is in cell N17 and my rows are 29-38. How do I go about making the cell N17 (Drop down menu) hide and un-hide rows 29-38. FYI row 29 would be product 1 in the drop down, row 30 would be product 2 in the drop down, row 31 would be product 3 in the drop down and so on.

Thanks,
Josh

 

[smozgur]

Hello Josh,

Welcome to the MrExcel Forum!

I assume you have 0-10 drop down in N17 cell with data validation.
Please copy and paste the following code into the sheet's module in VBE.

Worksheet_Change code is triggered when you change a cell value. First we look if the changed cell address is the one with the dropdown values ($N$17), then hide all product rows in 29:38, and unhide the selected number of rows in the dropdown cell.

It should give the idea.

Private Sub Worksheet_Change(ByVal Target As Range)
Dim rng As Range
    If Target.Address = "$N$17" Then
        With Range("29:38")
            .EntireRow.Hidden = True
            If Target > 0 Then
                .Resize(Target.Value).EntireRow.Hidden = False
            End If
        End With
    End If
End Sub

 

[Fluff]

How about

Private Sub Worksheet_Change(ByVal Target As Range)
   If Target.CountLarge > 1 Then Exit Sub
   If Target.Address(0, 0) = "N17" Then
      Rows("29:38").Hidden = True
      If Target > 0 Then Rows(29).Resize(Target).Hidden = False
   End If
End Sub

Beaten2it

 

[josh_oneill]

Suat!!!

You are the man!!! it works perfectly.
Really appreciate the help.

Thanks,
Josh

 

[smozgur]

Suat!!!

You are the man!!! it works perfectly.
Really appreciate the help.

Thanks,
Josh

You're welcome.
Glad to hear it helps.

 

[josh_oneill]

If I have multiple drop downs like this in the same sheet I have applied the same code to those input cells and rows but I get an error "Ambiguous name detected"
Do I need to change the "Private Sub Worksheet_Change" to something else?

Sorry new to VBA.

Thanks,
Josh

 

[smozgur]

You likely copied and pasted the same code into Sheet class module. You can have only one instance of the same sub procedure in the same module.

What you need to do is checking the second cell with the Target.Address, and add the second logic in the same code.

Let me now the second dropdown address, as well as the second range address to be hidden-unhidden.

 

[josh_oneill]

Second address:
N18 (Drop down), Rows 41-50

Third address
N19 (Drop down), Rows 53-62

Thanks,
Josh

 

[smozgur]

Instead using the same code for the different conditions, this time we will normalize our code, and reduce the number of code lines - which also provides better readability.

Please delete the old Worksheet_Change event procedure, and copy and paste the following one.

Note how we set a range object defined by rng variable, and set the corresponding range according to the changed dropdown.
Then we check if the variable is set (because it might be a cell other than N17, N18, N19 - which we don't want to execute the code in that case), and if variable is not "Nothing", we use rng to hide / unhide rows.

Hope this helps.

Private Sub Worksheet_Change(ByVal Target As Range)
Dim rng As Range
    If Target.Address = "$N$17" Then
        Set rng = Range("29:38")
    ElseIf Target.Address = "$N$18" Then
        Set rng = Range("40:51")
    ElseIf Target.Address = "$N$19" Then
        Set rng = Range("53:62")
    End If
    If Not rng Is Nothing Then
        With rng
            .EntireRow.Hidden = True
            If Target > 0 Then
                .Resize(Target.Value).EntireRow.Hidden = False
            End If
        End With
    End If
End Sub

 

[josh_oneill]

Hey Saut,

Works perfectly! Thanks again for your help. Really appreciate it.

Regards,
Josh