VBA to Loop through columns and hide columns totaling zero(0)

[JoeRooney]

Hi ,

I want to loop through my columns and hide all columns wherethe total value is 0.

The column range is (A:AEN) & the data begins from row 4 – to last row ( Lastrow is never the same and will always be based on last cell populated on columnA)

The columns can contain text, dates, numeric , blanks and zero’s(0) .

I would like the code to loop through each column and if thetotal equals zero(0) then hide the column , if the total is blank it is ok to not behidden.

I have the below code but it is not running and causing myexcel to restart.

Any help with this is greatly appreciated

Thanks,


Sub exa()



Dim MyCell As Range, MyRange AsRange

Set MyRange = Range("A:AEN")

For Each MyCell In MyRange

If MyCell.Value < 1 OrMyCell.Value = "" Then

MyCell.EntireColumn.Hidden = True

End If

Next MyCell



End Sub

 

[Fluff]

hoe about

Sub HideColumns()
   Dim i As Long, lr As Long
   lr = Range("A" & Rows.Count).End(xlUp).Row
   For i = 1 To 820
      Columns(i).Hidden = Application.Sum(Range(Cells(4, i), Cells(lr, i))) = 0
   Next i
End Sub

 

[JoeRooney]

Work perfectly, thank you

 

[Fluff]

Glad to help & thanks for the feedback

 

[JoeRooney]

Hi ,

Sorry to reopen this one but I have just noticed that thecode is hiding columns containing text as well as the blanks & zero’s.

Is it possible to add another condition to the ‘For i = 1 To820’ to include Text as well?

Thanks,

 

[Fluff]

How about

Sub HideColumns()
   Dim i As Long, lr As Long, x
   lr = Range("A" & Rows.Count).End(xlUp).Row
   For i = 1 To 820
      x = Evaluate("SumProduct(--IsText(" & Range(Cells(4, i), Cells(lr, i)).Address & "))")
      If x = 0 Then Columns(i).Hidden = Application.Sum(Range(Cells(4, i), Cells(lr, i))) = 0
   Next i
End Sub

 

[JoeRooney]

Hi ,

Thank you but that code no longer hides any columns , Thanksfor your help so far.

 

[Fluff]

It works for me on some test data, so I suspect that it's something to do with your sheet.
Are you sure that you don't have text in some of the columns that youl'd expect to be hidden?