I have 4 worksheets in 1 excel workbook. I am attempting to store Sheet1, Sheet2, and Sheet4 in an array. Then, I am wanting the program to hide all columns in the array that do not have the value "FY" in row 8. The purpose of this is to be able to view the FY rows with ease and to leave Sheet3 from hiding any columns.

Here is a screenshot of the excel file:

and here is my code snippet:

Sub FY_HIDE222()    

    Dim keyCells As Range

    Dim ws As Variant

    ws = Array("Sheet1", "Sheet2", "Sheet4")



    For Each sh In ws

        For Each keyCells In ws.Range("C8:ZZ8").Cells

            If keyCells.Value <> "FY" Then

                keyCells.EntireColumn.Hidden = True

            End If

        Next keyCells

    Next sh

End Sub

I am receiving a syntax error on this line:

For Each keyCells In ws.Range("C8:ZZ8").Cells

saying "keyCells = nothing"...Please assist in my thinking as I am thinking the keyCells variable should have FY, Q1, Q2, etc. stored.

Variable `sh is fetching string value only not Worksheet object. Try

Sub FY_HIDE222()

Dim keyCells As Range

Dim wsName As Variant

Dim ws As Worksheet

wsName = Array("Sheet1", "Sheet2", "Sheet4")

    For Each sh In wsName

        Set ws = ThisWorkbook.Sheets(sh)

        For Each keyCells In ws.Range("C8:ZZ8").Cells

            If keyCells.Value <> "FY" Then

                keyCells.EntireColumn.Hidden = True

            End If

        Next keyCells

    Next sh

End Sub

To keep it as simple as possible

Dim wsName, i As Long, c As Range

wsName = Array("Sheet1", "Sheet2", "Sheet4")



    For i = LBound(wsName) To UBound(wsName)

        For Each c In Sheets(i).Range("C8:ZZ8")

            If c.Value <> "FY" Then c.EntireColumn.Hidden = True

        Next c

    Next i

You can use union to gather qualifying ranges in the same sheet and hide in one go. If you want to go the extra mile you could re-write to loop the columns of the range and exit a loop of any given column at the first qualifying cell as you are hiding the entire column anyway.

Public Sub test()

    Dim wsName, i As Long, c As Range, unionRng As Range

    wsName = Array("Sheet1", "Sheet2", "Sheet4")



    For i = LBound(wsName) To UBound(wsName)

        For Each c In Worksheets(wsName(i)).Range("C8:ZZ8")

            If c.Value <> "FY" Then

                If Not unionRng Is Nothing Then

                    Set unionRng = Union(unionRng, c)

                Else

                    Set unionRng = c

                End If

            End If

        Next c

        If Not unionRng Is Nothing Then 

            unionRng.EntireColumn.Hidden = True

            Set unionRng = Nothing

        End If

    Next i

End Sub

Show Some, Show All

Option Explicit



Sub FY_HIDE222()



  Const cStrRange = "C8:ZZ8"

  Const cStrText = "FY"



  Dim objWs As Worksheet

  Dim objCell As Range

  Dim vntSheets As Variant

  Dim intCounter As Integer



  vntSheets = Array("Sheet1", "Sheet2", "Sheet4")



  For intCounter = LBound(vntSheets) To UBound(vntSheets)



    Set objWs = ThisWorkbook.Worksheets(vntSheets(intCounter))



    For Each objCell In objWs.Range(cStrRange)



      If objCell.Value <> cStrText Then

        objCell.EntireColumn.Hidden = True

      End If



    Next



  Next



End Sub

I have Excel 2003, so I used Z8 instead of ZZ8 which is obvious in the following picture.

Sub FY_SHOW222()



  Dim objWs As Worksheet

  Dim vntSheets As Variant

  Dim intCounter As Integer



  vntSheets = Array("Sheet1", "Sheet2", "Sheet4")



  For intCounter = LBound(vntSheets) To UBound(vntSheets)



    Set objWs = ThisWorkbook.Worksheets(vntSheets(intCounter))



    objWs.Columns.EntireColumn.Hidden = False



  Next



End Sub