Hide Column Based on Cell Value -

[jsw469]

I did search for this and could not find one that I could try to use.

I am try to find a macro that will look at Row 2 and if there is a "N/A" in that column it will hide the column. And of couse a code to unhide as well.

Excel 2010

 

[kpark91]

To Hide:

For i = 1 To Cells(2, Columns.Count).End(xlToLeft).Column
    If Cells(2, i) = "N/A" Then Cells(2, i).EntireColumn.Hidden = True
Next i

To unhide, you set .Hidden as False

 

[MrKowz]

I did search for this and could not find one that I could try to use.

I am try to find a macro that will look at Row 2 and if there is a "N/A" in that column it will hide the column. And of couse a code to unhide as well.

Excel 2010

Does it contain the literal string "N/A" or are you trying to hide an "#N/A!" error?

Public Sub HideNAString()
Dim i As Long
For i = 2 To Cells(2, Columns.Count).End(xlToLeft).Column
    Cells(2, i).EntireColumn.Hidden = Cells(2, i).Value = "N/A"
Next i
End Sub
 
Public Sub HideNAError()
Dim i As Long
For i = 2 To Cells(2, Columns.Count).End(xlToLeft).Column
    Cells(2, i).EntireColumn.Hidden = IsError(Cells(2, i).Value)
Next i
End Sub

 

[jsw469]

I am doing a series of Graphs and it is a created #N/A so that the headder will not show up on the graph then I am using the hide column to remove it from the legend.

 

[jsw469]

Public Sub HideNAString()
Thsi one worked. How do I do it to unhide the Column, I am used to the =False or True but not a .hidden

 

[MrKowz]

Public Sub HideNAString()
Thsi one worked. How do I do it to unhide the Column, I am used to the =False or True but not a .hidden

Public Sub UnhideAll()
Cells.EntireColumn.Hidden = False
End Sub

 

[jsw469]

AWESOME you rock TY for the Help

 

[MrKowz]

AWESOME you rock TY for the Help

No problem, thank you for the feedback.