Macro change row height based on row above

[Doofay]

I'm amending a workbook someone else created and I'm brand new to VB.

I've got a table that has over 200 rows 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 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 the trick, 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
Change the =1 to whatever you want in order for the code to trigger.

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?

The table is rows 12:217.

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?

 

[Jolly_Nolly]

Hi Doofay, welcome to the forum.

Replace the code that you have with this and see how you get on.

Private Sub Worksheet_Change(ByVal Target As Range)
Cells.RowHeight = 12.27
Range(Rows(Target.Row + 2), Rows(217)).RowHeight = 0
End Sub

Nolly

 

[Doofay]

Hi Nolly,

Thanks for the reply.

I must be doing something stupid. As soon as I paste the code into the VBA editor I can't run it. And it's disappeared from my list of macros.

I've tried setting up a new macro with the name Worksheet_Change by doing a record and then stopping straight away. I then pasted in the code but it did the same thing. I've tried taking out the Private but that made no difference. I also opened a new window in the editor and pasted it all as you have written it but it still wouldn't run or show up in my macro list.

What am I doing wrong?

Also will the macro affect the first 11 rows of the worksheet as these are the headers and such and I want these left alone if possible. Also I have 52 worksheets, one for each week, can I make it so this works on all of them. I think I have do something with Active.Sheet

Thanks.

 

[Doofay]

I've been messing round and found that whenever I put the (ByVal Target As Range) after the sub name, it makes the macro stop running and disappears from my macro list so it must be something to do with naming, that I don't understand.

Please help.

Thanks.

 

[Jolly_Nolly]

Sorry for the delay in getting back to you, had to go to work. Bummer!

Ok starting from the top.

Open excel. (Empty book for testing)
Right click on sheet1 tab and click on View Code.
Paste the code I provided in the big window on the right.
Close the VBA window.

On excel sheet1 type something in any cell.
What is supposed to happen is that all rows below the cell you typed into will shrink to 0. down to row 217. All rows above this will be normal and visible.

You cant call it because its private. It triggers when you change a cell value.

Does that help?

Nolly

 

[Doofay]

This is awesome, it's much faster than I put together today.

I've got 3 small problems:

1. The sheet is password protected so it fails when I enter new data. I know that in a normal macro I can put in a unprotect sheet at the beginning and re-protect sheet at the end. Can I do the same in this private macro and if yes where in the macro should I put the instructions?

2. It has altered the row height of all the rows 1-11 to 12.00. They are all set up as the headers of the sheet. Is there anyway it can be adapted to not affect the first 11 rows?

3. There are 52 sheets in the workbook, one for each week and 2 that are information sheets. I'm going to go through each sheet and paste the code in, but is there a quicker way of doing this or putting the code in to affect the 52 sheets but not the 2 info sheets?

Thanks,

Alex

 

[Jolly_Nolly]

Hi Alex

try this....

Private Sub Workbook_SheetChange(ByVal Sh As Object, ByVal Target As Range)
'replace sheet2 & sheet3 with the sheets that you don't want it to work on.
If Sh.Name = "Sheet2" Or Sh.Name = "Sheet3" Then
Else:
ActiveSheet.Unprotect
Rows(Target.Row + 1).RowHeight = 12.27
Range(Rows(Target.Row + 2), Rows(217)).RowHeight = 0
Range(Target.Address).Offset(1).Select
ActiveSheet.Protect
End If
End Sub

Paste this into the "ThisWorkbook" module.
This will trigger on all sheets except the ones that you specify as sheet2 or sheet3.

HTH

Nolly

 

[Doofay]

Hi Nolly,

This is great and has solved the 3 problems. The last one I have is;

The way the sheet works is when you enter a line of information into the week 1 sheet it copies it through the workbook so it will copy to the the other 51 weekly sheets, so anything entered on the week2 sheet will copy through to weeks 3-52. So week 52 sheet has every line that has been entered on previous sheets.

Now when I enter the info on the individual sheet the macro kicks in and the excess blank rows disappear, but they don't disappear on the subsequent following sheets. Is there anything that can be put in to the macro to update all the following sheets or do you think that will make it run slower.

Thanks,

Alex

 

[Doofay]

Hi Nolly,

Ignore the reply below. I'm not bothered about the future weeks now. The one issue I do have however is if you enter a new row then everything works fine but if you edit something on a row higher up, then it will shrink rows below that have information in them. Is there anyway of fixing it so that the any row with information in column B for example stays visible?

Thanks,

Alex