Macro to automatically hide rows that are empty

Hi there,

I'm trying to get my spreadsheet so that rows are hidden on one of my worksheets when the array formula produces no result (i.e. the cell is empty). Can anyone help?

Here is my current spreadsheet:
yearly summary test2.xls

On the worksheet tab "Yearly Summary" I'm trying to get it so that rows: 5-24, 28-47, 51-70, 74-93, all hide when the array formula in there produces nothing. Can anyone help?

Hopefully then the idea is that printing the page doesn't print the hidden rows.

Thanks for any help!!

I don't think xlBlanks will pick up cells with 0 value that still have a formula so your code will just hide row 25. You could do this with a loop like:

Please Login or Register  to view this content.

Wow awesome thank you!

Can I change it so that it checks columns A, G and M? (or just the entire row)

And how would I add in the other row ranges (28 to 47, 51 to 70, 74 to 93)?

Thanks for any help with this, I'm useless with VBA and am trying desperately to learn but am finding this stuff very difficult!

Please Login or Register  to view this content.

Thank you thank you thank you thank you!!!

Is there a way so that each time the sheet calculates all rows unhide themselves before these are hidden? It's just so that when I change the year the sheet is for it unhides them then just hides the relevant blank ones.

I tried adding this in at the beginning:

On Error Resume Next
ActiveSheet.Cells.EntireRow.Hidden = False

And for some reason when I tested it Excel got caught in an endless loop that caused it to crash completely...

What am I doing wrong? Do I need to put it as a separate macro?

Thanks again

That should be ok I think (note you can just use Activesheet.rows.hidden) not sure what would cause it to loop, unless something is triggering the event again within the macro. Maybe you could try turning events off while the macro runs:

Please Login or Register  to view this content.

Thank you so so so much!!

Is there any way so that it works when all cells are protected by password in the Worksheet (except B1)? Using:

On Error Resume Next

...?

When you protect the worksheet turn on userinterfaceonly

If you manually protect the sheet there is a tickbox in the options for protect worksheet

If you do it by code:
.protect userinterfaceonly:=True

I tried looking for the checkbox but in excel 2003 I couldn't find it. When I put the code in it asked me for the password to unprotect the sheet before unhiding any rows. Is there a way around that?

Thanks!!

Not sure on this, I thoguth that would have worked. The only way round it I could think of would be to unprotect the sheet before the code runs

Please Login or Register  to view this content.

Perfect thank you so much!!