Hiding columns based on cell values in column

[DickMaas]

Hi all,

I'm fairly new to VBA coding, but I managed to get most of the code done. It's just the last bit that's not working like I want it to.
My problem is the following:

In the specified range H55:BP85 I want excel to check for values in all the cells of each column (these are all outcomes of formula's). If the outcome of ALL cells in one column = 0 I want the column to be hidden.
I got this working for like 95%. The problem I got at the moment is that if the value of the cell in row 55 = 0, it allready hides the whole column instead of looping throughout the whole column before hiding. Because of this Excel hides columns where the value in row 55 = 0 but for instance the value in row 60 of the same column > 0.

I'm using the following code:



Dim rtest As Range
Dim Status As Integer

Set rtest = ActiveSheet.Range("H55", ActiveSheet.Range("H55:BP85").End(xlToRight))

For Each c In rtest.Columns
Status = 0
For Each cl In c.Cells
If cl.Value = 0 Then Status = Status + 1
Next cl
If Status = rtest.Rows.Count Then c.Hidden = True
Next c


Hope you guys can point me towards the mistake I'm making.

[大灰狼1976]

Hi DickMaas!
If you run code "rtest.Select", you will see that rtest has only one row.

[DickMaas]

You sir are my hero! Adjusted the range in rtest to Set rtest = ActiveSheet.Range(ActiveSheet.Range("H55"), ActiveSheet.Range("BP85").End(xlToRight)) and now it works perfectly!

Thank you so much, have been searching for the solution for days, will keep this trick in mind.

[大灰狼1976]

You're welcome:)