VBA - Hiding rows if other rows are hidden on different worksheets

[excelnoob1985]

Hi All,

I am looking to implement VBA code in my woorkbook whereby if a user hides / unhides any rows on Sheet 1 (for e.g. row 8), then the corresponding numbered row would automatically be hidden/shown on Sheet 2. By default all the rows are unhidden. I would appreciate any help!

Thanks,

 

[My Aswer Is This]

I think you would have to run a Vba Script like this:
If the row is hidden it will unhide the row on both sheets if it's not hidden it will hide the row

Sub Hide_Row()
Application.ScreenUpdating = False
Hide = InputBox("Hide What Row")
Rows(Hide).Hidden = True = Not Rows(Hide).Hidden = True
Sheets("Sheet2").Rows(Hide).Hidden = True = Not Sheets("Sheet2").Rows(Hide).Hidden = True
Application.ScreenUpdating = True
End Sub

 

[excelnoob1985]

My Aswer is this - that does work thank you!

I was wondering, rather than having to run the script each time when wanting to hide a row, would it be possible to have it auto run in the background and without an input box i.e. if I right clicked 'row 8' and hid it on Sheet 1, then 'row 8' on Sheet 2 would automatically hide? Similarly if I then unhid 'row 8' on Sheet 1, then 'row 8' on Sheet 2 would automatically unhide?

 

[My Aswer Is This]

There are no automatic on hide row events.
Right click on your sheet tab and choose view code
On the top left of page you will see General
Click on that and change it to Worksheet
Now look on top right and you will see Selection Change
Click on that and you will see a whole list of automatic events
You will see double click selection change and a whole list
You will see there is no On Hide row event

 

[excelnoob1985]

Thank you very much. I've just had a look and you are absolutely right. I've implemented the code as you very helpfully suggested!