Drop down with worksheet_change

**jwagman1** · 08-10-2012, 12:11 PM

So I am trying to use the answer to a drop down to hide a range of rows if a certain answer is selected. Here is the code Ive pieced together but it may not be right at all.. Help please?

Private Sub Worksheet_Change(ByVal Target As range)
 Dim what, whatrange, hold1, hold2, act, quotetype
 Dim laboronly As String
 
    hold1 = Worksheets(".").[G18].Value   ' First row of range
    hold2 = Worksheets(".").[G19].Value   ' Last row of range
    act = "A" & (hold1 - 3)
    what = hold1 & ":" & hold2
    quotetype = Worksheets("L&M").[E11].Value  'Cell with dropdown list
    laboronly = "Labor ONLY"     'Value of dropdown that will cause above range to hide
    If quotetype = laboronly Then
        range(what).EntireRow.Hidden = True
    ElseIf quotetype <> laboronly Then
        range(what).EntireRow.Hidden = False
    End If
End Sub

**tigeravatar** · 08-10-2012, 12:42 PM

jwagman1,

This code goes in the "L&M" sheet code module:

Private Sub Worksheet_Change(ByVal Target As Range)
    
    Dim lFirstRow As Long
    Dim lLastRow As Long
    
    If Target.Address = "$E$11" Then
        lFirstRow = Int(Sheets(".").Range("G18").Value2)
        lLastRow = Int(Sheets(".").Range("G19").Value2)
        
        'If the rows to hide are on sheet "." then change the Sheets() name
        Sheets("L&M").Rows(lFirstRow & ":" & lLastRow).Hidden = (Target.Value = "Labor ONLY")
    End If
    
End Sub

**jwagman1** · 08-10-2012, 02:35 PM

Works like a charm!! Thanks much for the help. You rock!

**tigeravatar** · 08-10-2012, 02:50 PM

You're very welcome

If that takes of your need, please mark this thread as solved.
New quick method:
Select Thread Tools-> Mark thread as Solved. To undo, select Thread Tools-> Mark thread as Unsolved.

Or you can use this way:
How to mark a thread Solved
Go to the first post
Click edit
Click Go Advanced
Just below the word Title you will see a dropdown with the word No prefix.
Change to Solved
Click Save