Excel VBA Hide/Unhide Columns Based on Cell Data

Question

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.

Application.Countif(Range("CS4:CS40"),">0") will tell you how many values are >0 — Tim Williams
– Tim Williams, Commented Sep 26, 2022 at 22:48

Tim Williams · Accepted Answer · 2022-09-26 22:52:46Z

1

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

answered Sep 26, 2022 at 22:52

Tim Williams

169k8 gold badges104 silver badges143 bronze badges

Sign up to request clarification or add additional context in comments.

1 Comment

Gamma Orion Over a year ago

Thank you ever so much. Seems to be working and should allow me to go much further getting the form where I want it.

Collectives™ on Stack Overflow

Excel VBA Hide/Unhide Columns Based on Cell Data

1 Answer 1

1 Comment

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

1 Comment

Your Answer

Sign up or log in

Post as a guest

Related