hide rows based on cell selection - VBA NOT WORKING

[SSPoulin23]

Good morning,

Ive searched and searched, and used old code that worked properly, however, now in this workbook it isn't.

PLEASE HELP!

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!

 

[dssJones]

Good morning,

Ive searched and searched, and used old code that worked properly, however, now in this workbook it isn't.

PLEASE HELP!

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!

Fortunately your tweak is easy: change your address to "$D$22"

 

[SSPoulin23]

OMG YOU AREN'T KIDDING! don't I feel silly. thank you so much!!!!

 

[dssJones]

P.S.: You can put the merge back on, as long as D22 is in the North West corner of the merge, and change your code to add as the first line:

Set Target = Target.cells(1,1)