hide rows based on cell selection

SSPoulin23

New Member
Joined
Nov 21, 2014
Messages
44
Good morning,

In cell D22 the user makes a selection - if they choose "No Cost Incurred", I would like rows 33 through 37 to automatically hide. However, if they change the selection, I want the rows to be visible again. D22 has a data validation list and is a merged cell (though I've attempted to remove validation and unmerge the cell to make it work again it hasn't helped).

I've been trying to get this to work, but to no avail:

Private Sub Worksheet_Change(ByVal Target As Range)

'Hide BusOfficeApproval based on D22(registration) entry
If Target.Address = "D22" Then
If Target.Value = "No Cost Incurred" Then
'Hides Rows
ActiveSheet.Rows("33:37").EntireRow.Hidden = True
Else
'Makes row visible
ActiveSheet.Rows("33:37").EntireRow.Hidden = False
End If
End If
End Sub




if there is a better way, I'm all ears, or if there is a simple tweak, that'd be great too. THANK YOU!
 
Try this

Code:
Private Sub Worksheet_Change(ByVal Target As Range)
    If Range("D20").Value = "NO COST INCURRED" Then
        Rows("33:37").EntireRow.Hidden = True
    ElseIf Range("D20").Value = "INCURRED" Then
        Rows("33:37").EntireRow.Hidden = False


    End If
End Sub
 
Upvote 0
didn't work. I reposted and someone pointed out that I missed the $ on the cell reference. changing D22 to $D$22 did the trick. thank you!!
 
Upvote 0

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
Back
Top