Hiding columns based on cell value

[Red over White]

I am trying to hide columns where the Value in the cells on row 9 is "", that cell being populated with a formula where the result is "". However I am getting the Compile Error Message 'Next without For'. Any clues?

Sub GraphC()

Dim a As Integer
Dim ColumnVar As Variant

ColumnVar = Array("B", "C", "D", "E", "F", "G", "H", "I", "J", "K")

For a = 0 To 9
With Sheets("GraphC")
If Cells(a, "9").Value = "" Then
.Columns(a).EntireColumn.Hidden = True
End If
Next a
End With
End Sub

 

[VoG]

Try

Sub GraphC()
Dim a As Integer
With Sheets("GraphC")
    For a = 2 To 11
        .Columns(a).Hidden = .Cells(9, a).Value = ""
    Next a
End With
End Sub

 

[Red over White]

That works - many thanks

 

[Rick Rothstein]

While I am pretty sure Peter's code is more efficient, I thought I would post this alternative for its possible interest value...

Sub HideBlankFormulasInRow9()
  Dim Arr As Variant
  With Range("A9:K9")
    Arr = .Formula
    .Value = .Value
    .SpecialCells(xlCellTypeBlanks).EntireColumn.Hidden = True
    .Formula = Arr
  End With
End Sub

 

[johnlucasprice]

The above code works great, but I have a question:


Sub GraphC()Dim a As IntegerWith Sheets("GraphC") For a = 2 To 11 .Columns(a).Hidden = .Cells(9, a).Value = "" Next aEnd WithEnd Sub</PRE>
How do I get it to work so that when I make copies of GraphC or change the sheet name it still works? I might copy GraphC 20 times and rename it "MS", "HS", "Elem", etc.

In my file it looks more like this:

Private Sub Worksheet_Activate()
Dim a As Integer
With Sheets("Bldg Dashboard")
For a = 18 To 26
.Columns(a).Hidden = .Cells(4, a).Value = ""
Next a
End With
End Sub

If I copy "Bldg Dashboard" and rename it to "High School", how can I get the code to work on that tab? I'm so close and this is so cool, please help.

 

[johnlucasprice]

Is there a way to change:

With Sheets("Bldg Dashboard")

to

With AllSheets?

(sorry, I'm not a code guru)

 

[VoG]

Sorry, it is unclear what you want.

 

[johnlucasprice]

My code works on one tab right now ("Bldg Dashboard" tab).

Private Sub Worksheet_Activate()
Dim a As Integer
With Sheets("Bldg Dashboard")
For a = 18 To 26
.Columns(a).Hidden = .Cells(4, a).Value = ""
Next a
End With
End Sub

When I update a value on an input tab, it changes values on Bldg Dashboard tab. I'm good with that. However, I would like the freedom to copy Bldg Dashboard tab and rename it to anything I want. Is there a way to alter the code so it doesn't explicity list a tab/sheet name? I might copy that tab 20 times and rename them all.

 

[johnlucasprice]

This worked:


Private Sub Worksheet_Activate()
Dim a As Integer
With Me
For a = 18 To 26
.Columns(a).Hidden = .Cells(4, a).Value = "Hide"
Next a
End With
End Sub