Updating Target Cell To Hide/Unhide After Deleting Rows

[kaybin]

Hello,

I am very new to VBA code. I am trying to write a macro where based on the response of a target cell, a number of rows will hide or unhide. Here is the code that I currently have, and it works.

Private Sub Worksheet_Change(ByVal Target As Range)

If Range("C62") = "Yes" Then
Range("63:84").EntireRow.Hidden = False
End If

If Range("C62") = "No" Then
Range("63:84").EntireRow.Hidden = True
End If

End Sub

However, the response cell "C62" is not always constant. I will often delete or add rows above the response cell. What can I add to update my code after a row addition/deletion?

Thanks.

 

[JoeMo]

You could name the response cell. Call it "ResponseCell" for example. Then use the name in your code like this:

Private Sub Worksheet_Change(ByVal Target As Range)
Select Case Range("ResponseCell")
    Case "Yes": Rows("63:84").Hidden = False
    Case "No": Rows("63:84").Hidden = True
End Select
End Sub

Excel will keep track of the named cell even if you insert or remove rows above it.