Excel VBA Hide/Unhide Columns Based on Cell Data-CodePudding

I am attempting to have a weekly calendar where hours for employees working for multiple sites automatically hides columns (representing days of the week) where no one worked.

So Site A would have columns B-H (Monday to Sunday) and Site B would then carry on from columns I-P (also Monday to Sunday). The rows would be individual employee names. The Cell data would be hours worked from a "0" (representing no hours worked) all the way up to "8". If there is no data in a given column (meaning no one worked at that specific site on that specific day) I would like to column to hide automatically. Thus far I have been attempting to get a working version for just a single column but failing.

My effort at a single column currently looks like this:

Private Sub ToggleButton14_Click()

Dim cell As Range
    For Each cell In ActiveWorkbook.ActiveSheet.Columns("CS").Cells
        If cell.Value = "0" Then
            cell.EntireColumn.Hidden = True
        ElseIf cell.Value > "0" Then
            cell.EntireColumn.Hidden = False
            End
        End If
    Next cell

End Sub

The list of things wrong with this code is lengthy. I cannot seem to structure the If Else to hide the column if all of the data is "0" but show the column if any of the data is ">0". I cannot get the If Else to focus on a specific range of cells for each column "CS4:CS40" so as to avoid the cells with dates at the top of the column. I have tried various versions of the code and reading the documentation but I have been woefully in effective and none of the similar questions on stack overflow seem to give me something close enough to work with.

CodePudding user response：

Maybe like this:

Dim ws As Worksheet, col As Range

Set ws = ActiveSheet

For Each col In ws.Range("B4:P40").Columns 'adjust range as needed
    'Hide the column if no values are >0
    col.EntireColumn.Hidden = (Application.CountIf(col, ">0") = 0)
Next col