hiding/unhiding rows based on dropdown selection

[Legacy 219053]

I am trying to write VBA to hide or unhide a number of rows based on the selection of a dropdown list. I am brand new to VBA and although I was able to piece this together by exploring the internet, it is not working in my worksheet. Honestly, I just copied, pasted and edited this code and I am not exactly sure what its commands mean. I am using Excel 2010 and my dropdown list was made with data validation.

Private Sub Worksheet_Change(ByVal Target As Range)
    If Target.Address = "$c$11" Then
        Range("27,28,32,33,41,42,56,57,64,65,69,70,77,78,84,85,94,95,112:129,190,191,194,195,198,199,202,203,237:249").EntireRow.Hidden = False
        If Target = "Corn" Then
            Rows("27,28,32,33,41,42,56,57,64,65,69,70,77,78,84,85,94,95,112:129,190,191,194,195,198,199,202,203,237:249").EntireRow.Hidden = True
        ElseIf Target = "Soybeans" Then
            Rows("26,28,31,33,40,42,55,57,63,65,68,70,76,78,83,85,93,95,99:111,121:129,189,191,193,195,197,199,201,203,231:236,244:249").EntireRow.Hidden = True
        ElseIf Target = "Wheat" Then
            Rows("26,27,31,32,40,41,55,56,63,64,68,69,76,77,83,84,93,94,99:120,189,190,193,194,197,198,201,202,231:243").EntireRow.Hidden = True
        End If
    End If
End Sub

 

[VoG]

You need a capital C

If Target.Address = "$C$11" Then

The code has to go in the sheet's code module (right click the sheet tab, View Code).

 

[Nefarious]

I'm also attempting the same thing only I used the following code:

Private Sub Worksheet_Change(ByVal Target As Range)
     If Not Intersect(Target, Range("$C$12")) Is Nothing Then
        With Range("$C$12")
            Range("turbine_rm, generator_rm, gas_modules, status_lights").EntireRow.Hidden = .Value = "PANEL, FIRE PROTECTION, 6 GAS MODULE"
            Range("status_lights, turbine_rm, generator_rm").EntireRow.Hidden = .Value = "PANEL, FIRE PROTECTION, 8 GAS MODULE"
            Range("turbine_rm, generator_rm").EntireRow.Hidden = .Value = "PANEL, FIRE PROTECTION, 8 GAS MODULE WITH STATUS LIGHTS"
            Range("panel, turbine_rm").EntireRow.Hidden = .Value = "F&G DETECTION COMPONENTS (Generator Enclosure)"
            Range("panel, generator_rm").EntireRow.Hidden = .Value = "F&G DETECTION COMPONENTS (Turbine Enclosure)"
        End With
    End If
     
End Sub

My problem is that only the last condition works properly while the other conditions either partially work (only 1 of the ranges are hidden) or nothing is hidden. I also tried to use the OP's code which gave me an error on the first row command. What am I missing?

 

[Legacy 219053]

Thanks VoG, I did not know that would result in an error and have since changed that cell reference. Unfortunately, it is not yet working. I am putting this code in the area that comes up when I right click on the sheet tab and click View Code. You say the word "module", I'm not supposed to put it in the folder "Modules" that appears at the bottom of the VBA Project screen which is in the left hand side of the VB editor, am I?

My error occurs in the third line of my code and says: Error '1004' Method "Range" of object "_Worksheet" failed

Am I not referencing these ranges properly? This will be my third time attempting to use VBA, so it's very possible that I have made a small error that I am not aware of.

 Private Sub Worksheet_Change(ByVal Target As Range)
    If Target.Address = "$C$11" Then
            Range("27,28,32,33,41,42,56,57,64,65,69,70,77,78,84,85,94,95,112:129,190,191,194,195,198,199,202,203,237:249").EntireRow.Hidden = False
        If Target = "Corn" Then
            Range("27,28,32,33,41,42,56,57,64,65,69,70,77,78,84,85,94,95,112:129,190,191,194,195,198,199,202,203,237:249").EntireRow.Hidden = True
        ElseIf Target = "Soybeans" Then
            Range("26,28,31,33,40,42,55,57,63,65,68,70,76,78,83,85,93,95,99:111,121:129,189,191,193,195,197,199,201,203,231:236,244:249").EntireRow.Hidden = True
        ElseIf Target = "Wheat" Then
            Range("26,27,31,32,40,41,55,56,63,64,68,69,76,77,83,84,93,94,99:120,189,190,193,194,197,198,201,202,231:243").EntireRow.Hidden = True
        End If
    End If
End Sub

 

[VoG]

Try like this (you will need to modify each line of code)

If Target.Address = "$C$11" Then
    Range("C27,C28,C32,C33,C41,C42,C56,C57,C64,C65,C69,C70,C77,C78,C84,C85,C94,C95,C112:C129,C190,C191,C194,C195,C198,C199,C202,C203,C237:C249").EntireRow.Hidden = False

 

[Legacy 219053]

Ok, so I have tried a new approach, with simpler code, but I am still getting the same 1004 error about "range of worksheet failed". The debugger stops and highlights the fourth line in yellow on this error. Can someone please advise me as to what I am doing wrong?

Private Sub Worksheet_Change(ByVal Target As Range)
     If Not Intersect(Target, Range("C11")) Is Nothing Then
        With Range("C11")
            Range("27,28,32,33,41,42,56,57,64,65,69,70,77,78,84,85,94,95,112:129,190,191,194,195,198,199,202,203,237:249").EntireRow.Hidden = .Value = "Corn"
            Range("26,28,31,33,40,42,55,57,63,65,68,70,76,78,83,85,93,95,99:111,121:129,189,191,193,195,197,199,201,203,231:236,244:249").EntireRow.Hidden = .Value = "Soybeans"
            Range("26,27,31,32,40,41,55,56,63,64,68,69,76,77,83,84,93,94,99:120,189,190,193,194,197,198,201,202,231:243").EntireRow.Hidden = .Value = "Wheat"
        End With
    End If
     
End Sub