Condition Hidden Columns

[smitpau]

Hi,

I'd like columns to be hidden based on the value in a certain row, basically if the value in say row 2 equals "N/A" the column should be hidden. It sounds relatively simple but would prefer a range type VBA code instead of having to type out each column one by one.

Thanks for reading.

Paul

 

[My Aswer Is This]

Hi,

I'd like columns to be hidden based on the value in a certain row, basically if the value in say row 2 equals "N/A" the column should be hidden. It sounds relatively simple but would prefer a range type VBA code instead of having to type out each column one by one.

Thanks for reading.

Paul

So if you enter "N/A" in row 2 of column A you want what other columns also hidden
So like columns 2 to 12
Do you want a Inputbox to popup asking what columns?

 

[smitpau]

Kind of so say column A,B & C said "N/A" these would be hidden but columns D&E as they are not "N/A" would still stay visible.
The Inputbox is fine as long as it's only done once, i.e. the end user would not need to select them.

Thanks

 

[My Aswer Is This]

Well if you entered NA in each column you want hidden then this could work but you earlier said:
but would prefer a range type VBA code instead of having to type out each column one by one.

 

[Fluff]

How about

Sub smitpau()
   Dim Cl As Range
   
   For Each Cl In Range("A2", Cells(2, Columns.Count).End(xlToLeft))
      Cl.EntireColumn.Hidden = Cl.Value = "N/A"
   Next Cl
End Sub

 

[smitpau]

Thanks Fluff that works anyway to make that automatic, guess it just overload Excel probably as about 120 column range would be included.

 

[Fluff]

Do you have lots of formulae on that sheet?

 

[My Aswer Is This]

Thanks Fluff that works anyway to make that automatic, guess it just overload Excel probably as about 120 column range would be included.

This could be done with on sheet change event code. So if you entered NA into a certain cell in one sheet the script could hide each column you wanted but some how you would have to tell it what columns

 

[smitpau]

Do you have lots of formulae on that sheet?

Yes

 

[Fluff]

In that case you can turn of calculation like

Sub smitpau()
   Dim Cl As Range
   
   Application.Calculation = xlCalculationManual
   For Each Cl In Range("A2", Cells(2, Columns.Count).End(xlToLeft))
      Cl.EntireColumn.Hidden = Cl.Value = "N/A"
   Next Cl
   Application.Calculation = xlCalculationAutomatic
End Sub

Are the values in row the result of a formula, or manually changed?