Hide Range Code Note Working

jUStPunkin

New Member
Joined
Mar 23, 2009
Messages
45
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
 

Some videos you may like

Excel Facts

Round to nearest half hour?
Use =MROUND(A2,"0:30") to round to nearest half hour. Use =CEILING(A2,"0:30") to round to next half hour.

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

New Member
Joined
Mar 23, 2009
Messages
45
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
 

Watch MrExcel Video

Forum statistics

Threads
1,122,514
Messages
5,596,611
Members
414,080
Latest member
penguin23

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