Hide columns with VBA

[crew11k]

I have a .xlsx spreadsheet with 10 columns and 2 rows (A1:J10). If A1 is blank I want to hide columns A through J. If B1 is blank I want to hide columns B through J. If cell C1 is blank, I want to hide columns C through J etc. How do I write code such that once the columns are hidden, if the value in B1 changes, it will unhide the column. I think it is a select a case type case but am not sure how to write this. Any thoughts or suggestions would be greatly appreciated.

Thanks

Ron

 

[Smitty]

Welcome to the Board!

How with the values be changing? Via formula or manually?

 

[crew11k]

Thank you for the kind welcome. A formula linking from another worksheet will be the cause of the change in value which would trigger the unhide or hide column. Does this help?

 

[Smitty]

You could use a calculate event, first unhiding all of the columns, then evaluate each cell for your criteria. If it matches, then hide the columns, otherwise show them.

With the calculate event and a range of cells to evaluate you generally use a For...Each...Next construct:

<font face=Calibri><SPAN style="color:#00007F">Private</SPAN> <SPAN style="color:#00007F">Sub</SPAN> Worksheet_Change(<SPAN style="color:#00007F">ByVal</SPAN> Target <SPAN style="color:#00007F">As</SPAN> Range)<br>    <SPAN style="color:#00007F">Dim</SPAN> c <SPAN style="color:#00007F">As</SPAN> Range<br>    <br>    Application.ScreenUpdating = <SPAN style="color:#00007F">False</SPAN><br>        Cells.Columns.EntireColumn.Hidden = <SPAN style="color:#00007F">False</SPAN><br>        <br>        <SPAN style="color:#00007F">For</SPAN> <SPAN style="color:#00007F">Each</SPAN> c <SPAN style="color:#00007F">In</SPAN> Range("A1:J1")<br>            <SPAN style="color:#00007F">If</SPAN> c.Value = 0 <SPAN style="color:#00007F">Then</SPAN><br>                Range(Cells(1, c.Column), Cells(1, "J")).EntireColumn.Hidden = <SPAN style="color:#00007F">True</SPAN><br>            Else: Cells(1, c.Column).EntireColumn.Hidden = <SPAN style="color:#00007F">False</SPAN><br>            <SPAN style="color:#00007F">End</SPAN> <SPAN style="color:#00007F">If</SPAN><br>        <SPAN style="color:#00007F">Next</SPAN> c<br>    Application.ScreenUpdating = <SPAN style="color:#00007F">True</SPAN><br>    <br><SPAN style="color:#00007F">End</SPAN> <SPAN style="color:#00007F">Sub</SPAN></FONT>

HTH,