Worksheet.Change event to hide blank rows

[Fishboy]

Hi all,

I am working on an Excel 2010 workbook and am looking to create a worksheet.change event to hide specific rows if they are blank, or show them if they are not.

I have a number of tables which use formulas to populate cells based on values elsewhere in the workbook, which means sometimes rows have data in and sometimes not. I am looking to have the worksheet.change code look at specific cells (defined in the code) and if the referenced cell is blank hide the row.

For example:

I want to look at C35:C43, and for every blank cell the rest of that row needs to hide.
Changes elsewhere in the workbook might suddenly populate a previously blank cell in range C35:C43, at which point the corresponding row needs to unhide.

I don't know if I can use the above range to achieve this, or whether there needs to be a specific line of code for each row.

Can anyone offer any insight at all please?

 

[mumps]

Hi Fishboy. You won't be able to do this with a worksheet.change event because the event will not be triggered by a change in the cell if the change is the result of a formula.

 

[Fishboy]

Hi mumps, thanks for the response. I take it even a simple formula such as =('SheetName'!C130) would not allow the code to trigger?

 

[mumps]

That's correct.

 

[Fishboy]

Dang

Do you have any suggestions to dynamically hide rows based on cell value, or would it have to be an actual macro that I would need to press to get it to run as desired?

 

[mumps]

I suspect you would have to use a macro unless some other forum member has a different approach.

 

[Fishboy]

Thanks for the feedback mate. I guess I will have to look into alternate methods for this.

Cheers