Hiding sepecific column on based of condition

[ravinder_tig]

Hi Guys
I do need your help regarding one of projects in which i had to hide a certain column on a specific condition
in this case column heading 3,4(G,H) and 7,8(K,L) should be hide
the function i created for this is it checks the range for value "N/A" only for Visible columns
if it matches a value which is
Number of visible columns - 3 (as data start from column 4) - 6 number of hidden columns
but the problem is i'm not able to deal with hidden condition
i'm attaching the file as well function

Public Function colhide()
    Dim i As Integer, _
        c As Integer
    Dim MyLastRow As Long, _
        MyLastCol As Long
    Dim MyRange2 As String, _
        MyLastCell As String, _
        Str As String, _
        MyColLett As String
    Dim Cell As Range
    With Worksheets("All Summary")
    Application.DisplayAlerts = False
    
    On Error Resume Next
    MyLastRow = .Cells(Rows.Count, "D").End(xlUp).Row
    MyLastCol = .Cells(3, Columns.Count).End(xlToLeft).Column
    For c = 5 To MyLastCol
        i = 0
        Str = .Cells(4, c).Address
        MyColLett = Split(Columns(c).Address(False, False), ":")(0)
        MyRange2 = Str & ":" & "$" & MyColLett & "$" & MyLastRow
        For Each Cell In Range(MyRange2)
            If InStr(Cell.Value, "N/A") <> 0 And .Cells(c, MyColLett).Hidden = False Then
                i = i + 1
            End If
        Next
        If i = MyLastRow - 3 - 6 Then      ' Where 6 is number of hidden columns (Variale for diff sheets)
            MsgBox ("Column is hidden")
            'ActiveSheet.Range(MyRange2).EntireColumn.Hidden = True
        End If
    Next
    Application.DisplayAlerts = True
    End With
End Function

Your help would be gr8ly appriciated
Regards,
Ravinder S

[mdmackillop]

I'm not really clear what you are after. It is unneccesary to check each row in a column. Do you need more than this?

[VBA]
Sub TestHidden()
Dim MyRange As Range
Set MyRange = Range(Cells(3, 5), Cells(3, Columns.Count).End(xlToLeft))
For Each cel In MyRange
If cel.EntireColumn.Hidden = True Then MsgBox "Column is hidden"
'or if only some columns to be identified
Select Case cel.Column
Case 7, 8, 11, 12
If cel.EntireColumn.Hidden = True Then MsgBox "Column " & cel.Column & " is hidden"
End Select
Next
End Sub

[/VBA]

[ravinder_tig]

thanks a lot mdmackillop vbmenu_register("postmenu_187357", true); but my problem is i don't hav fix columns in this case i was jst giving the example that i had to hide certain column7,8,11,12

what i had to do is match the range below only for visible rows if that range is full of "N/A" column should hide

[mdmackillop]

[vba]Sub TestHidden()
Dim MyRange As Range
Dim c As Range, cel as Range
Set MyRange = Range(Cells(3, 5), Cells(3, Columns.Count).End(xlToLeft))

For Each cel In MyRange
Set c = cel.Offset(1).Resize(21).SpecialCells(xlCellTypeVisible)
If Application.CountIf(c, "N/A") = c.Cells.Count Then
cel.EntireColumn.Hidden = True
MsgBox "Column " & cel.Column & " is hidden"
End If
Next
End Sub
[/vba]