Auto Hide Rows Based On Cell Above

I've attached a screenshot to illustate what I am doing and a one tab version of the workbook. I've had to do it in a zip folder as they were too big individually.

I've got a table that starts at row 12 and finishes at row 217 and the users enter information into the rows over a year. The creator of the sheet has set it up so there is a "z" in the second cell of each row and as this cell is overtyped with the new information the row changes colour and is included in the selected print macro that is set up.

I want to add in a macro that changes the row height to 0 based on the "z" being present in the row above 2nd cell. So all that is showing in the table are the rows that have info in them and one blank one underneath. So everytime a new row of info is entered either a new line will reveal itself underneath or there is a control button on the sheet that the user can press to reveal a new empty line.

I don't know how to write VB, but I've found some code online that claims to do what I need, but I need it to be altered to use the presence of the "z" in the row above (2nd column) as the trigger for the rule:


Private Sub Workbook_SheetChange(ByVal Sh As Object, ByVal Target As Range)

If Target.Value = 1 Then

Rows(Target.Row).RowHeight = 0

Else

End If

End Sub

Or should I be starting off with minimised rows and changing it so the height increases as the "z" in the row above is overtyped?

Can this happen automatically as the z is overtyped or does the macro need to be triggered by a control button for example?

Is there a better way to do this? I don't want to get rid of all the extra empty rows and have a macro to create a new row for 2 reasons: 1.They have formulas and macros running set up by the creator that I don't want to mess with and 2. There are 52 sheets in the workbook, 1 for each week of the year and the next sheet takes the information from the previous weeks sheet so on the last sheet, number 52, it has every line that has been entered over the year from week one to week 51 carried over. If I created a new row on week2, I would have to then create that row on every sheet following week 2 and I think that would make it more complicated. I would need the macro to be able to run on any of the 52 sheets.


Private Sub Worksheet_Change(ByVal Target As Range)

Cells.RowHeight = 12.27

Range(Rows(Target.Row + 2), Rows(217)).RowHeight = 0

End Sub