Hiding Columns

[lehigh46]

Hi All,

I know this sounds a bit far fetched but, is it possible to hide
columns based on the contents of a row of cells?

ie: if columns R, S & U have zero results then hide those columns

Thank you for not laughing and thank you for your help.

Tom Snyder

 

[Guest]

Not farfetched Tom:

You could write a macro that would detect the zero values and set the column
widths to zero or set columnhide to true.

 

[Guest]

Tom

This macro will do the trick for you

Sub HideCols()
Dim j As Integer, rng As Range
' unhide columns R to U
Range("R1:U1").EntireColumn.Hidden = False
For j = 18 To 19
Set rng = Range(Cells(1, j), Cells(65536, j))
If Application.CountA(rng) > 0 Then
Columns(j).Hidden = False
Else: Columns(j).Hidden = True
End If
Next
Set rng = Range(Cells(1, 21), Cells(65536, 21))
If Application.CountA(rng) > 0 Then
Columns(j).Hidden = False
Else: Columns(j).Hidden = True
End If
End Sub

Open a Module, Alt + F11, Add a module and copy the macro.
Return to your worksheet and choose Tools, Macro, Run Macro (or just press
Alt + F8) select the macro and press run.

You can assign a shortcut key or draw a command button on your worksheet and
assign the macro to the command.

Regards
Peter