Hide columns based on cells = 0

[DoosanRuss]

Good Afternoon All

I've found this code on the tin-ter-web and it works

Private Sub Worksheet_Calculate()
Application.ScreenUpdating = False
Dim c As Range
    For Each c In Range("K8:CD8")
        c.EntireColumn.Hidden = (c.Value = 0)
    Next c
Application.ScreenUpdating = True
End Sub

however my Range is K8 to CD12, when i change the code to CD12 it hides all the columns regardless of cells containing data greater than Zero.

What i would like the code to do is look at cells K8, K9, K10, K11 & K12 if they all equal "0" i want Excel to hide the column
If any of those cells are greater than "0" then the column stays unhidden.

then move on to the next column J8, J9 ... etc

All help gratefully recieved

BR

Russ

 

[Scott Huish]

Have no idea what the tin-ter-web is.

Column J is not in your specified range.

How are the values in your range being changed? Manually or by formula?

 

[DoosanRuss]

Oh Jeez! i meant 'L' not J good god.. hard day methinks, never good when you can't do your abc's

The Range is being changed by formula

BR

Russ

P.S tin-ter-web is internet, sorry i'd just been speaking to a Mancunian

 

[Scott Huish]

Private Sub Worksheet_Calculate()
Dim c As Range
Application.EnableEvents = False
    For Each c In Range("K8:CD8")
        c.EntireColumn.Hidden = Application.WorksheetFunction.CountIf(c.Resize(5), 0) = 5
    Next
Application.EnableEvents = True
End Sub