VBA conditional column hiding

[Junk80]

Hi,

I've been trying to hide certain columns based on the value of cells in the 2nd row.
I have tried the following code, but for some reason it won't hide anything. Protection is not an issue, all sheets are unprotected prior to the running of this code. To state the blindingly obvious myself: there are cells in the 2nd row with "Act." or "Plan", respectively.

Worksheets("Growth").Activate
For a = 1 To 34
If Cells(2, a) = "Act." Then
   Columns(a).EntireColumn.Hidden = True
End If
Next a
 
Worksheets("VOP").Activate
For b = 1 To 34
If Cells(2, b) = "Plan" Then
   Columns(b).EntireColumn.Hidden = True
End If
Next b

Additionally, I also want to hide the row/column headings etc. in all sheets:

For Each sh In ThisWorkbook.Worksheets
    ActiveWindow.DisplayHeadings = False
    ActiveWindow.DisplayGridlines = False
    Application.DisplayFormulaBar = False
Next sh

Though I doubt whether it is strictly neccesary to do this for each sheet, even when I try it like this the headings in one sheet still remain visible. This problem only occurs in this one sheet, and only for the headings; gridlines and formulabar are hidden.

Any help would be greatly appreciated

(Also posted on Ozgrid.com)

Junk
​

 

[Kaps_mr2]

try :-

If Cells(2, a) = "Act." Then
   Worksheets("Growth").Columns(a).EntireColumn.Hidden = True
End If
Next a
 

For b = 1 To 34
If Cells(2, b) = "Plan" Then
   Worksheets("VOP").Columns(b).EntireColumn.Hidden = True
End If
Next b

thanks

Kaps

 

[Junk80]

Nope, that didn't do the trick...

Nevertheless, it is always better to avoid activate.

 

[Junk80]

Re: VBA conditional column hiding [SOLVED]

Posted a tad too fast there...

With a little alteration it does work.

For a = 1 To 34
If Worksheets("Growth").Cells(2, a) = "Act." Then
Worksheets("Growth").Columns(a).EntireColumn.Hidden = True
End If
Next a
[/code
 
Thanks Kaps
 
Junk

 

[Peter_SSs]

Re: VBA conditional column hiding [SOLVED]

Where was the original code you placed? If it was in a standard module I think it should have worked. If in a sheet module (not 'Growth' or 'VOP') it would not work because any unreferenced ranges will be taken to belong to the sheet the code is in, not the ActiveSheet.

 

[Junk80]

You're right of course, Peter. The code is in fact located in a sheet module, I didn't really stop to think about that... Live and learn

Thanks for pointing that out
Junk

 

[Junk80]

By the by, in case anyone is still interrested, the other problem I was having with hiding row/column headings is also solved. Simply needed to activate each sheet (obviously! otherways ActiveWindow wouldn't work.): sh.Activate added.

cheers
J