I've only copied a portion of the code I'm working with but its not working to hide unhide rows based on the various selections from 9 separate drop downs. The code should also update the hidden rows when any selection is changed or the clearcontents macro is ran. The closest I can get this is showing only the most recent selection but I need to show the results for all selections at the same time.
Option Explicit
Private Sub InIt()
Application.EnableEvents = True
End Sub
Private Sub Worksheet_SelectionChange(ByVal Target As Range)
Dim i As Range
Dim u As Range
Dim Cel As Range
Set u = Union(Range("J10"), Range("N10"), Range("R10"), Range("V10"), Range("N14"), Range("J14"), Range("N14"), Range("R10"), Range("V14"), Range("I23"))
Set i = Intersect(u, Target)
If i Is Nothing Then Exit Sub
For Each Cel In i
Select Case Target.Value
Case "Yes"
Rows("24:56").Hidden = False
Case "Potatoes (table) growing"
Rows("63:63").Hidden = False
Case "Potatoes (seed) growing"
Rows("64:64").Hidden = False
Case "Growing vegetables and melons and selling them at roadside stands"
Rows("65:65").Hidden = False
Case "Growing vegetables and melons and selling them at market gardens / farmers markets"
Rows("66:66").Hidden = False
Case "Grain growing"
Rows("67:68").Hidden = False
Case "Market gardening except greenhouses"
Rows("69:69").Hidden = False
Case "Mixed vegetables growing except greenhouses"
Rows("70:70").Hidden = False
Case "Citrus groves and farms"
Rows("71:73").Hidden = False
Case "Fruit and tree nut combination farming"
Rows("74:76").Hidden = False
Case "Fruit growers (e.g. pome and stone fruits)"
Rows("77:79").Hidden = False
Case "Non citrus fruit farming"
Rows("80:82").Hidden = False
Case "Small berry fruit farming (e.g. blackberry, blueberry, currant, dewbery, loganberry, raspberry)"
Rows("83:85").Hidden = False
Case "Herb farming (e.g. ginseng, echinacea, etc.) except greenhouses"
Rows("86:86").Hidden = False
Case "Christmas tree growing"
Rows("87:89").Hidden = False
Case "Turf (sod) farming"
Rows("90:90").Hidden = False
Case "Floriculture except greehouses"
Rows("91:91").Hidden = False
Case "Alfalfa, clover, hay or timothy farming"
Rows("92:92").Hidden = False
Case "Fruit and vegetable combination"
Rows("93:93").Hidden = False
Case "Maple syrup and products production"
Rows("94:99").Hidden = False
Case "Combination field crop farming"
Rows("100:101").Hidden = False
Case "Hemp growing"
Rows("102:103").Hidden = False
Case "Hops and grain growing, combination"
Rows("104:104").Hidden = False
Case "Peanut farming"
Rows("105:106").Hidden = False
Case "Seed growers"
Rows("107:108").Hidden = False
Case "Sugar beet farming and grain growing, combination"
Rows("109:110").Hidden = False
Case "Tea farming"
Rows("111:112").Hidden = False
Case "Beef cattle feedlots"
Rows("113:121").Hidden = False
Case "Beef cattle ranching"
Rows("122:130").Hidden = False
Case "Beef cattle finishing - Grassers seasonal"
Rows("131:131").Hidden = False
Case "Dairy cattle and milk production"
Rows("132:136").Hidden = False
Case "Hog - Farrow to Finish - Secondary operations only"
Rows("137:139").Hidden = False
Case "Hog - Finishing only - Secondary operations only"
Rows("140:142").Hidden = False
Case "Chicken egg production"
Rows("143:149").Hidden = False
Case "Broiler and other meat-type chicken production"
Rows("150:156").Hidden = False
Case "Caponor or Cornish hens farming"
Rows("157:163").Hidden = False
Case "Turkey production"
Rows("164:170").Hidden = False
Case "Egg hatcheries, poultry"
Rows("171:179").Hidden = False
Case "Combination poultry and egg production"
Rows("180:186").Hidden = False
Case "Ostrich or emu farming"
Rows("187:188").Hidden = False
Case "Pheasant, geesem, goose, duck quail or guinea fowl farming"
Rows("189:190").Hidden = False
Case "Pigeon or squab farming"
Rows("191:1920").Hidden = False
Case "Lamb or sheep farming, including feedlots"
Rows("193:203").Hidden = False
Case "Milk production, goat farming"
Rows("204:206").Hidden = False
Case "Aquaculture inland operations only"
Rows("207:207").Hidden = False
Case "Apiaries - honey"
Rows("208:211").Hidden = False
Case "Leafcutter bees raising"
Rows("212:213").Hidden = False
Case "Gathering of wild mushrooms and truffles"
Rows("214:214").Hidden = False
Case "Wild berry picking"
Rows("215:215").Hidden = False
Case "Worm gathering"
Rows("216:216").Hidden = False
Case "Custom crop spraying"
Rows("217:225").Hidden = False
Case "Custom granular application (spreading fines)"
Rows("226:232").Hidden = False
Case "Custom seed treating"
Rows("233:235").Hidden = False
Case "Custom soil amendment application"
Rows("236:241").Hidden = False
Case "Custom harvesting"
Rows("242:243").Hidden = False
Case "Custom mobile grain cleaning service"
Rows("244:245").Hidden = False
Case "Custom planting or seeding"
Rows("246:247").Hidden = False
Case "Custom tillage or land breaking"
Rows("248:249").Hidden = False
Case "Orchard cultivation services"
Rows("250:251").Hidden = False
Case "Agronomy"
Rows("252:252").Hidden = False
Case "Farm produce (e.g., fruit, vegetable) packing service"
Rows("253:253").Hidden = False
Case "Seed and / or chemical distributors"
Rows("254:257").Hidden = False
Case "Fruit picking service, hand (e.g., apple, strawberry, blueberry, cherry)"
Rows("258:258").Hidden = False
Case "Custom grain drying service"
Rows("259:261").Hidden = False
Case "Grain fumigation service"
Rows("262:266").Hidden = False
Case "Custom haying and/or chopping"
Rows("267:268").Hidden = False
Case "Hulling and shelling of almonds, filberts, nuts, peanuts, pecans and walnuts"
Rows("269:269").Hidden = False
Case "Animal semen collection, production and storage services"
Rows("270:273").Hidden = False
Case "Artificial insemination services, animal specialties and livestock"
Rows("274:275").Hidden = False
Case "Breeding services for livestock"
Rows("276:277").Hidden = False
Case "Brand inspector"
Rows("278:278").Hidden = False
Case "Catching poultry, with no hauling"
Rows("279:280").Hidden = False
Case "Cattle dehorning and hoof trimming services"
Rows("281:281").Hidden = False
Case "Chick sexing service"
Rows("282:282").Hidden = False
Case "Cleaning poultry houses"
Rows("283:283").Hidden = False
Case "Custom manure hauling, corral and feedlot cleaning, service"
Rows("284:286").Hidden = False
Case "Egg grading station, fee-based"
Rows("287:287").Hidden = False
Case "Embryo transplant service, agricultural"
Rows("288:289").Hidden = False
Case "Farriers (horseshoeing)"
Rows("290:293").Hidden = False
Case "Fur pelting service"
Rows("294:294").Hidden = False
Case "Sheep dipping and shearing services"
Rows("295:296").Hidden = False
Case "Vaccinating livestock (except by veterinarians)"
Rows("297:299").Hidden = False
Case "Electrician (no alarm testing or hook up), farm based business"
Rows("300:306").Hidden = False
Case "Plumbing and heating contractors, farm based business"
Rows("307:330").Hidden = False
Case "Plumbing contractors, farm based business"
Rows("331:338").Hidden = False
Case "Assembly and installation for others of agricultural equipment in farm outbuildings, farm based business"
Rows("339:344").Hidden = False
Case "Automatic gate installation (e.g., garage, lane), farm based business"
Rows("345:350").Hidden = False
Case "Drywall finishing (e.g., sanding, spackling, stippling, taping, texturing), farm based business"
Rows("351:357").Hidden = False
Case "Drywall installation, including the hanging of drywall, sheetrock, plasterboard, gypsum wallboard and subsequent finishing, farm based business"
Rows("358:364").Hidden = False
Case "Heavy machinery painting, farm based business"
Rows("365:372").Hidden = False
Case "Painting contractors, farm based business"
Rows("373:382").Hidden = False
Case "Flooring contractors, excluding hardwood refinishing, farm based business"
Rows("383:383").Hidden = False
Case "Tile and terrazzo contractors, farm based business"
Rows("384:384").Hidden = False
Case "Deck and fence construction (wooden excluding pasture fencing), farm based business"
Rows("385:390").Hidden = False
Case "Brush clearing or cutting, farm based business"
Rows("391:395").Hidden = False
Case "Cutting of rights-of-way contractor, farm based business"
Rows("396:400").Hidden = False
Case "Land clearing contractors farm based business"
Rows("401:407").Hidden = False
Case "Power, communication and pipelines, rights of way clearance (except maintenance), farm based business"
Rows("408:412").Hidden = False
Case "Septic tanks and weeping tile installation, farm based business"
Rows("413:431").Hidden = False
Case "Brick pavers installation (e.g., driveways, patios and sidewalks), farm based business"
Rows("432:437").Hidden = False
Case "Concrete patio construction, farm based business"
Rows("438:446").Hidden = False
Case "Fences and corral installation, erection or repair, farm based business"
Rows("447:452").Hidden = False
Case "Mail boxes erection (single residential/farm - outdoor), farm based business"
Rows("453:455").Hidden = False
Case "Post Hole digging contractors, farm based business"
Rows("456:456").Hidden = False
Case "Dog and cat food manufacturing, excluding supplement additives, farm based business"
Rows("457:461").Hidden = False
Case "Other animal food manufacturing, excluding supplement additives, farm based business"
Rows("462:462").Hidden = False
Case "Barley feed, chopped, crushed or ground, excluding supplement additives, manufacturing, farm based business"
Rows("463:465").Hidden = False
Case "Bird food manufacturing, excluding supplement additives, farm based business"
Rows("466:466").Hidden = False
Case "Complete livestock feed manufacturing, excluding supplement additives, farm based business"
Rows("467:469").Hidden = False
Case "Specialty feed manufacturing excluding supplement additives (e.g., for mice, guinea pig, mink, earthworm, rabbit), farm based business"
Rows("470:470").Hidden = False
Case "Micro and macro feed premixes manufacturing for animals, excluding dogs and cats or supplement additives, farm based business"
Rows("471:471").Hidden = False
Case "Milling grain to make livestock feed excluding supplement additives, farm based business"
Rows("472:474").Hidden = False
Case "Pet food manufacturing excluding dogs and cats or supplement additives, farm based business"
Rows("475:475").Hidden = False
Case "Shell crushing and grinding for animal feed, excluding supplement additives, farm based business"
Rows("476:476").Hidden = False
Case "Flour milling, farm based business"
Rows("477:479").Hidden = False
Case "Rice or corn milling and malt manufacturing, farm based business"
Rows("480:483").Hidden = False
Case "Tapioca manufacturing, farm based business"
Rows("484:486").Hidden = False
Case "Wet grain or corn milling, farm based business"
Rows("487:488").Hidden = False
Case "Edible and inedible oilseed products, farm based business"
Rows("489:490").Hidden = False
Case "Oilseed crushing mills, farm based business"
Rows("491:492").Hidden = False
Case "Oils made from purchased oils (e.g., rapeseed (canola), cottonseed, soybean, olive, peanut, linseed, palm-kernel, sunflower), farm based business"
Rows("493:495").Hidden = False
Case "Breakfast cereal manufacturing, farm based business"
Rows("496:498").Hidden = False
Case "Sugar manufacturing, farm based business"
Rows("499:505").Hidden = False
Case "Dried beet pulp manufacturing, farm based business"
Rows("506:511").Hidden = False
Case "Non-chocolate confectionery manufacturing, farm based business"
Rows("512:517").Hidden = False
Case "Cake ornaments & edible confectionery manufacturing, farm based business"
Rows("518:522").Hidden = False
Case "Candied fruit & fruit peel products manufacturing (e.g., candied, glazed, crystallized), farm based business"
Rows("523:526").Hidden = False
Case "Chewing gum manufacturing, farm based business"
Rows("527:529").Hidden = False
Case "Corn confections manufacturing (e.g., popcorn balls, candy-coated popcorn products), farm based business"
Rows("530:534").Hidden = False
Case "Cough drops or lozenges manufacturing (except medicated), farm based business"
Rows("535:537").Hidden = False
Case "Sugared and stuffed dates manufacturing, farm based business"
Rows("538:542").Hidden = False
Case "Energy food bar manufacturing, farm based business"
Rows("543:547").Hidden = False
Case "Fudge manufacturing, farm based business"
Rows("548:552").Hidden = False
Case "Granola bars and clusters manufacturing, farm based business"
Rows("553:557").Hidden = False
Case "Halvah manufacturing, farm based business"
Rows("558:562").Hidden = False
Case "Marshmallows manufacturing, farm based business"
Rows("563:567").Hidden = False
Case "Covered nuts manufacturing, farm based business"
Rows("568:572").Hidden = False
Case "Synthetic chocolate, marzipan or toffee manufacturing, farm based business"
Rows("573:575").Hidden = False
Case "Confectionery manufacturing from purchased chocolate, farm based business"
Rows("576:580").Hidden = False
Case "Chocolate-covered granola bars made from purchased chocolate, farm based business"
Rows("581:585").Hidden = False
Case "Drinks, cocoa powder or instant chocolate made from purchased chocolate, farm based business"
Rows("586:588").Hidden = False
Case "Chocolate-covered nuts made from purchased chocolate, farm based business"
Rows("589:591").Hidden = False
Case "Frozen food manufacturing, farm based business"
Rows("592:597").Hidden = False
Case "Blast freezing on a contract basis, farm based business"
Rows("598:598").Hidden = False
Case "Frozen fruit and vegetable juice concentrates manufacturing, farm based business"
Rows("599:604").Hidden = False
Case "Dietetic, homogenized or low sodium frozen foods, farm based business"
Rows("605:610").Hidden = False
Case "Frozen whipped topping manufacturing, farm based business"
Rows("611:616").Hidden = False
Case "Fruit and vegetable canning, pickling & drying, farm based business"
Rows("617:621").Hidden = False
Case "Bouillon canning, farm based business"
Rows("622:626").Hidden = False
Case "Broth canning (except seafood), farm based business"
Rows("627:631").Hidden = False
Case "Canned pasta manufacturing, farm based business"
Rows("632:636").Hidden = False
Case "Canning soups (except seafood), farm based business"
Rows("637:641").Hidden = False
Case "Catsup & similar tomato sauces manufacturing, farm based business"
Rows("642:646").Hidden = False
Case "Chili con carne canning, farm based business"
Rows("647:651").Hidden = False
Case "Chinese foods canning, farm based business"
Rows("652:656").Hidden = False
Case "Dehydrating fruits and vegetables (except sun drying), farm based business"
Rows("657:658").Hidden = False
Case "Freeze-drying fruits & vegetables, farm based business"
Rows("659:664").Hidden = False
Case "Gravy canning, farm based business"
Rows("665:669").Hidden = False
Case "Canned hominy manufacturing, farm based business"
Rows("670:674").Hidden = False
Case "Horseradish canning (except sauce), farm based business"
Rows("675:679").Hidden = False
Case "Infant and junior food canning, farm based business"
Rows("680:684").Hidden = False
Case "Pork and beans canning, farm based business"
Rows("685:689").Hidden = False
Case "Potato products dehydrating (e.g., flakes, granules), farm based business"
Rows("690:694").Hidden = False
Case "Preserves, jams and jellies manufacturing, farm based business"
Rows("695:699").Hidden = False
Case "Relishes canning, farm based business"
Rows("700:704").Hidden = False
Case "Dry salad dressing mixes, farm based business"
Rows("705:709").Hidden = False
Case "Dry Sauce mixes, farm based business"
Rows("710:714").Hidden = False
Case "Sauerkraut manufacturing, farm based business"
Rows("715:719").Hidden = False
Case "Fluid milk manufacturing, farm based business"
Rows("720:724").Hidden = False
Case "Cottage cheese manufacturing, farm based business"
Rows("725:729").Hidden = False
Case "Cream manufacturing, farm based business"
Rows("730:734").Hidden = False
Case "Sour-cream based dips manufacturing, farm based business"
Rows("735:740").Hidden = False
Case "Fresh non-alcholic eggnog manufacturing, farm based business"
Rows("741:745").Hidden = False
Case "Fluid milk substitutes manufacturing, farm based business"
Rows("746:750").Hidden = False
Case "Milk-based drinks manufacturing (except dietary), farm based business"
Rows("751:755").Hidden = False
Case "Non-dairy liquid creamers manufacturing, farm based business"
Rows("756:761").Hidden = False
Case "Sour cream substitutes manufacturing, farm based business"
Rows("762:767").Hidden = False
Case "Sour cream manufacturing, farm based business"
Rows("768:772").Hidden = False
Case "Substitute milk products manufacturing, farm based business"
Rows("773:778").Hidden = False
Case "Whipping cream manufacturing, farm based business"
Rows("779:783").Hidden = False
Case "Yogurt manufacturing (except frozen), farm based business"
Rows("784:789").Hidden = False
Case "Butter, cheese & dry and condensed dairy product manufacturing, farm based business"
Rows("790:793").Hidden = False
Case "Anhydrous butterfat manufacturing, farm based business"
Rows("794:797").Hidden = False
Case "Animal feed dry milk products manufacturing, farm based business"
Rows("798:800").Hidden = False
Case "Baby formula fresh, processed & bottled manufacturing, farm based business"
Rows("801:806").Hidden = False
Case "Butter, creamery & whey manufacturing, farm based business"
Rows("807:810").Hidden = False
Case "Dry & wet casein manufacturing, farm based business"
Rows("811:812").Hidden = False
Case "Cheese manufacturing, farm based business"
Rows("813:817").Hidden = False
Case "Cheese spreads manufacturing, farm based business"
Rows("818:822").Hidden = False
Case "Imitation, substitute or analog cheese manufacturing, farm based business"
Rows("823:827").Hidden = False
Case "Condensed, evaporated or powdered whey manufacturing, farm based business"
Rows("828:831").Hidden = False
Case "Dried & powdered cream manufacturing, farm based business"
Rows("832:835").Hidden = False
Case "Dehydrated milk manufacturing, farm based business"
Rows("836:839").Hidden = False
Case "Dairy & non-dairy base drinks manufacturing, farm based business"
Rows("840:844").Hidden = False
Case "Ice milk mix manufacturing, farm based business"
Rows("845:849").Hidden = False
Case "Milk concentrated, condensed, dried, evaporated or powdered manufacturing, farm based business"
Rows("850:853").Hidden = False
Case "Milk UHT (ultra-high temperature) manufacturing, farm based business"
Rows("854:858").Hidden = False
Case "Non-dairy dry creamers manufacturing, farm based business"
Rows("859:863").Hidden = False
Case "Nonfat dry milk manufacturing, farm based business"
Rows("864:867").Hidden = False
Case "Soy beverages manufacturing, farm based business"
Rows("868:872").Hidden = False
Case "Substitute butter & cheese manufacturing, farm based business"
Rows("873:877").Hidden = False
Case "Liquid raw whey, farm based business"
Rows("878:881").Hidden = False
Case "Yogurt mix manufacturing, farm based business"
Rows("882:886").Hidden = False
Case "Ice cream, frozen pops & frozen dessert manufacturing, farm based business"
Rows("887:892").Hidden = False
Case "Sorbets or sherbets manufacturing, farm based business"
Rows("893:898").Hidden = False
Case "Tofu frozen desserts manufacturing, farm based business"
Rows("899:904").Hidden = False
Case "Frozen yogurt manufacturing, farm based business"
Rows("905:910").Hidden = False
Case "Custom slaughtering, farm based business"
Rows("911:911").Hidden = False
Case "Custom cut & wrap, farm based business"
Rows("912:918").Hidden = False
Case "Custom slaughtering and cut & wrap, farm based business"
Rows("919:925").Hidden = False
Case "Frozen meat pies (i.e., tourtières), made from purchased meat, farm based business"
Rows("926:931").Hidden = False
Case "Processed meat (except poultry) made from purchased meat (i.e., pastrami, salami, smoked meat, bologna, weiners, sausage), farm based business"
Rows("932:936").Hidden = False
Case "Meat curing, drying, salting, smoking or pickling, made from purchased meat, farm based business"
Rows("937:941").Hidden = False
Case "Pigs' feet, cooked and pickled, made from purchased meat, farm based business"
Rows("942:9446").Hidden = False
Case "Poultry processing (except baby or pet food), farm based business"
Rows("947:949").Hidden = False
Case "Poultry processed meat manufacturing (e.g., hot dogs, luncheon meats, sausages), farm based business"
Rows("950:954").Hidden = False
Case "Rabbits slaughtering, processing & dressing (i.e., fresh, frozen, canned or cooked), farm based business"
Rows("955:958").Hidden = False
Case "Slaughtering, dressing & packing small game, farm based business"
Rows("959:960").Hidden = False
Case "Fish & seafood, curing, drying, pickling, salting, smoking, canning, chowders and soups fresh or frozen, farm based business"
Rows("961:965").Hidden = False
Case "Seafood & seafood products fresh prepared or frozen manufacturing, farm based business"
Rows("966:969").Hidden = False
Case "Seaweed processing (e.g., dulse), farm based business"
Rows("970:973").Hidden = False
Case "Shucking & packing fresh shellfish, farm based business"
Rows("974:975").Hidden = False
Case "Retail bakeries stands or markets, farm based business"
Rows("976:979").Hidden = False
Case "Commercial bakery products fresh or frozen, farm based business"
Rows("980:984").Hidden = False
Case "Communion wafers manufacturing, farm based business"
Rows("985:989").Hidden = False
Case "Cracker meal and crumbs manufacturing, farm based business"
Rows("990:994").Hidden = False
Case "Crackers, cookies & biscuits manufacturing (e.g., graham, saltine, soda), farm based business"
Rows("995:999").Hidden = False
Case "Ice cream cones & wafers manufacturing, farm based business"
Rows("1000:1004").Hidden = False
Case "Zwieback & rusk manufacturing, farm based business"
Rows("1005:1009").Hidden = False
Case "Flour mixes, dough, pie crust shells, pasteries, prepared batters & pasta manufacturing, farm based business"
Rows("1010:1014").Hidden = False
Case "Packaging dry pasta that are manufactured with other ingredients, farm based business"
Rows("1015:1019").Hidden = False
End Select
Next Cel
If Not Application.Intersect(u, Target) Is Nothing Then
If Application.Intersect(u, Target) Is Nothing Then Exit Sub
Application.EnableEvents = False
Rows("24:56").Hidden = True
Rows("63:1824").Hidden = True
End If
Application.EnableEvents = True
End Sub