hiding/unhiding rows based on dropdown selection

crazyhorse12

New Member
Joined
Jun 25, 2012
Messages
21
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.

Code:
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
 

Some videos you may like

Excel Facts

Select a hidden cell
Somehide hide payroll data in column G? Press F5. Type G1. Enter. Look in formula bar while you arrow down through G.

VoG

Legend
Joined
Jun 19, 2002
Messages
63,650
You need a capital C

Code:
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

Board Regular
Joined
Mar 3, 2005
Messages
61
I'm also attempting the same thing only I used the following code:

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?
 

crazyhorse12

New Member
Joined
Jun 25, 2012
Messages
21
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.

Code:
 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

Legend
Joined
Jun 19, 2002
Messages
63,650
Try like this (you will need to modify each line of code)

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
 

crazyhorse12

New Member
Joined
Jun 25, 2012
Messages
21
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?

Code:
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
 

Watch MrExcel Video

Forum statistics

Threads
1,122,653
Messages
5,597,377
Members
414,141
Latest member
Joey_T92

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
Top