Macro to hide columns with empty cells.

I am trying to formulate some code that will hide a range of columns if particular cells are empty in that column.

Attached is where I am after hacking around code found on here.

I am looking to filter the column under the date. then run code to remove all the empty columns it but only looking for blank cells in the range E27:DK100.

This it wlll then create a condensed version of the chart suitable for printing.

Any ideas would be appreciated.

thanks will give it a go when i'm in work tommorow.

works great but.....
After i've filtered a column under the date. Is it possible to also hide columns that apprear empty but have hidden data due to the filter?

Originally Posted by Leith Ross

Hello bailey73,

Can you provide me with a couple of short before and after examples?

Leith,

I actually have the same question as bailey73. I took the same macros that you created in this example (thank you by the way) and applied them to my spreadsheet (see attached). In my example, I have names listed in column E, and associated projects listed in Row 5, Columns H:BV. Then, in columns C and D, I have identifiers for each name. I would like to sort for P's (for example) in column C. Then, there will be 11 rows that only show up. Is there a way to modify the macro to make all the activities in Row 5, Columns H:BV hide if there is nothing listed for the names in the sort? Does that make sense? In addition, if I need to modify the number of activities (Row 5) over time, will it break the macro? I am actually willing to use any solution so long as it is pretty simple.

I appreciate any help that you can provide. I will admit that I am fairly rookie at VB/Macros, but I can usually reverse engineer most things.

Thank you,

John

Originally Posted by Leith Ross

Hello jnm1985,

Welcome to the Forum!

I am not sure I understand what you want to do. Do you want to hide the cells "H5:BV5" whenever the user filters column "C" or something else ?

So my end goal would be to only show the activities being completed by individuals by the grouping that I select. For example, if I only wanted to see what activities the names in P's (column C) are working on. With the current data, that means I would only want to see columns H, I, K, L, N, P, Q, R, etc. because each of these activities are being worked on by someone in bucket "P" (located in column C). Does that make sense?

So my thoughts were some sort of macro that would show only the people listed under P, C, L, or S and only the associated activities being worked on by that selected bucket.

Thanks again!

Originally Posted by Leith Ross

Hello John,

Here are the revised macros to hide and unhide the empty columns after the AutoFilter has been applied. These have been added to the attached workbook.

Module2 Macros

Please Login or Register  to view this content.

Leith,

I really appreciate your help.

Thank you very much,

John