DickMaas
05-29-2019, 12:41 AM
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.
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.