Hide columns based on row values
 

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

Hide columns based on row values
 

Hi Brendan,

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

"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.
--
Win XP PRof SP2 / Vista Ultimate SP2
Office 2003 SP2 /2007 Ultimate SP2

Hide columns based on row values
 

On Friday, March 7, 2014 3:40:29 PM UTC-5, wrote:
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

Brilliant. Thank you Claus. Worked perfectly!