Hide Range Code Note Working

jUStPunkin

Board Regular
Joined
Mar 23, 2009
Messages
52
I'm a relative newbie at VBA, but I have a question about some code I've written. I've actually copied and modified this from another routine I've written (which works), but for some reason, I can't get this to work.

I have a cell where you choose one of four options (straight, tee, H or other), and based on your selection, certain rows should be hidden. It's not working though. Can anybody see what I've done wrong?

Code:
Private Sub Worksheet_Change(ByVal Target As Range)
    Select Case Worksheets("Nozzle").Range("$H$10")
        Case Is = "Straight"
            Worksheets("Nozzle").Range("$A$28:$D$31").EntireRow.Hidden = True
            Worksheets("Nozzle").Range("$A$25:$D$31").EntireRow.Hidden = False
        Case Is = "Tee"
            Worksheets("Nozzle").Range("$A$30:$D$31").EntireRow.Hidden = True
            Worksheets("Nozzle").Range("$A$25:$D$31").EntireRow.Hidden = False
        Case Is = "H"
            Worksheets("Nozzle").Range("$A$25:$D$31").EntireRow.Hidden = False
            Worksheets("Nozzle").Range("$A$25:$D$31").EntireRow.Hidden = False
        Case Is = "Other"
            Worksheets("Nozzle").Range("$A$25:$D$31").EntireRow.Hidden = False
            Worksheets("Nozzle").Range("$A$25:$D$31").EntireRow.Hidden = False
        Case Else
            Worksheets("Nozzle").Range("$A$25:$D$31").EntireRow.Hidden = False
            Worksheets("Nozzle").Range("$A$25:$D$31").EntireRow.Hidden = False
                        
        End Select
        
         
End Sub
 

Excel Facts

Which lookup functions find a value equal or greater than the lookup value?
MATCH uses -1 to find larger value (lookup table must be sorted ZA). XLOOKUP uses 1 to find values greater and does not need to be sorted.

Crizznitch

Board Regular
Joined
Nov 18, 2004
Messages
120
I think if you switch the lines hiding code and unhiding code, it should solve your problem.

Currently it is hiding rows, and then unhiding the rows right after that.
 

jUStPunkin

Board Regular
Joined
Mar 23, 2009
Messages
52
I think if you switch the lines hiding code and unhiding code, it should solve your problem.

Currently it is hiding rows, and then unhiding the rows right after that.


Put the falses first, in other words.

Thanks, I'll give it a try.
 

Crizznitch

Board Regular
Joined
Nov 18, 2004
Messages
120
Yep.

Also, you could shorten the code with:

Code:
        Case Is = "Straight"
            Worksheets("Nozzle").Rows("25:31").Hidden = False
            Worksheets("Nozzle").Rows("28:31").Hidden = True
        Case Is = "Tee"
            Worksheets("Nozzle").Rows("25:31").Hidden = False
            Worksheets("Nozzle").Rows("30:31").Hidden = True
 
Master Excel Bundle

Excel contains over 450 functions, with more added every year. That’s a huge number, so where should you start? Right here with this bundle.

Forum statistics

Threads
1,167,651
Messages
5,854,956
Members
431,689
Latest member
jacker01

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