Hide columns based on row values

[brendanlince]

Hi all,

I am trying to put together a macro to achieve the following:

I have formulas in row 5 of my worksheet in columns A through VF. These formulas return in each cell from A5 through VF5 either the result "PRINT" or "" (blank). I need the code for a macro which will say:

"if A5 = "PRINT", then do not hide column A. If A5 = "" (blank/empty cell - other than the formula that in that cell) then hide column A. I want it then to do that same for columns B through VF so that the columns for each cell that returns "PRINT" will not be hidden and the columns for each cell that return "" will be hidden.

To be even more of a pest, if there is any way for this macro to run in real time (ie, to recheck, based on user entries in the worksheet, whether a column should be hidden without the user having to manually run the macro) then that would be wonderful...

Any help you could all give would be greatly appreciated.

B

 

[Claus Busch]

Hi Brendan,

Am Fri, 7 Mar 2014 12:40:29 -0800 (PST) schrieb (e-mail address removed):

"if A5 = "PRINT", then do not hide column A. If A5 = "" (blank/empty cell - other than the formula that in that cell) then hide column A. I want it then to do that same for columns B through VF so that the columns for each cell that returns "PRINT" will not be hidden and the columns for each cell that return "" will be hidden.

try:

Sub HideCols()
Dim rngC As Range

Application.ScreenUpdating = False
For Each rngC In Range("A5:VF5")
rngC.EntireColumn.Hidden = (rngC <> "PRINT")
Next
Application.ScreenUpdating = True
End Sub


Regards
Claus B.

 

[brendanlince]

Hi all,



I am trying to put together a macro to achieve the following:



I have formulas in row 5 of my worksheet in columns A through VF. These formulas return in each cell from A5 through VF5 either the result "PRINT" or "" (blank). I need the code for a macro which will say:



"if A5 = "PRINT", then do not hide column A. If A5 = "" (blank/empty cell - other than the formula that in that cell) then hide column A. I wantit then to do that same for columns B through VF so that the columns for each cell that returns "PRINT" will not be hidden and the columns for each cell that return "" will be hidden.



To be even more of a pest, if there is any way for this macro to run in real time (ie, to recheck, based on user entries in the worksheet, whether acolumn should be hidden without the user having to manually run the macro)then that would be wonderful...



Any help you could all give would be greatly appreciated.



B

Brilliant. Thank you Claus. Worked perfectly!