Column Hiding based on cell value

[RiciH83]

Hi.

I have a formula that runs across all of row 1. What im wanting to do is hide all columns that contain #n/a in that row.

Im very new to vba and would appreciate any help with this. Its a shame there is no horizontal autofilter in excel

Thank you

 

[wigi]

Hello there

You can consider:

Sub hidecolumnswitherrors()
    
    Application.ScreenUpdating = False
    Columns.EntireColumn.Hidden = False
    On Error Resume Next
    Rows(1).SpecialCells(xlCellTypeFormulas, xlErrors).EntireColumn.Hidden = True

End Sub

Every error will give rise to hiding the column, not only #N/A.

 

[RiciH83]

Thank you for that,

After a bit of trial and error i just managed to get this to work.

Private Sub HideColumns()
Application.ScreenUpdating = False
Dim c As Range, rng
Set rng = Range("C1:DV1")
For Each c In rng
If c.Value = "H" Then
c.EntireColumn.Hidden = True
Else
c.EntireColumn.Hidden = False
End If
Next c
End Sub

Just replaced #n/a with a "H" for Hide.

Thank you for your help though. That will come in handy for some other things im working on