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

 

[josh_oneill]

If I would like to also include a column hide function how would I go about doing this?
Input cell is E17 (Drop down menu) where it lists 1 Year, 2 Years, 3 Years, 4 Years, 5 Years.
Then the columns I wish to hide and un-hide are F-J.

I tried tweaking the existing code to look at columns but I broke the code...

Thanks,
Josh

 

[smozgur]

In that case, we need to hide / unhide columns, the current code's logic hiding / unhiding rows won't help it.
However, we can extend it to make it work.

I wrote some notes in the code, so it explains the logic.

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")
    ElseIf Target.Address = "$E$17" Then
        'Note: setting range to be columns instead rows
        Set rng = Range("F:J")
    End If
    If Not rng Is Nothing Then
        With rng
            'Note: need to know if the range consist of columns or row
            'because we need to use EntireColumn or EntireRow properties accordingly
            'This can be done by using other ways, but I used this one here
            'Looking at the row count that rng resides, and if it is equal to
            'rng row count, it means it is a column
            'Do not stuck here, you can even use a variable called isColumn
            'to set true / false in the conditions above and use it to know
            'if you are dealing with hiding rows or columns instead.
            If .EntireColumn.Rows.Count = .Rows.Count Then
                'We are dealing with columns
                .EntireColumn.Hidden = True
                If Target > 0 Then
                    .Resize(, Target.Value).EntireColumn.Hidden = False
                End If
            Else
                'We are dealing with rows
                .EntireRow.Hidden = True
                If Target > 0 Then
                    .Resize(Target.Value).EntireRow.Hidden = False
                End If
            End If
        End With
    End If
End Sub

 

[josh_oneill]

Hey Suat,

I get a "run-time error '1004': application-defined or object-defined error"

It didnt like the following:
.Resize(, Target.Value).EntireColumn.Hidden = False

Any thoughts?

 

[smozgur]

Oh, you should actually have "1 Year", "2 Years" ... in the drop down instead numbers from 1 to 5 like the previous ones.

Replace that line with the following:

.Resize(, Left(Target.Value, 1)).EntireColumn.Hidden = False

This will take the number part and work correctly. It can be done different, but you get the idea.

 

[josh_oneill]

Once again thank you Suat! Works perfectly now.

 

[smozgur]

Once again, Welcome to the MrExcel Message Board!