Macro to Hide Columns with ZERO value

[share_knowledge]

Hi -

Could someone please show me a better way to hide all columns with zero value in my excel 2003 file? It would be great that i could have a macro embed in my workbook/sheet that automatically hide all these zero value columns and only show the ones with valid values. Currently, i have to mananually select all columns with zero value and hide them one by one. This takes so much time since i have more than 6-7k rows. Thanks!

--Tony

 

[iliace]

Any column that has at least one zero value should be hidden?

 

[share_knowledge]

Actually i wanted to hide the only columns with ZERO value in ALL rows? Here is an example:

Column A with 100 rows where there is one row with a value greater one = I want this column to be visible.

Column B with 100 rows where there is ZERO value in ALL rows = I'd like to automatically hide this column with a macro or with any quick way.

Can i also have another macro that do the same job but for ROW hidden instead of COLUMN? For the task in have on hand, i just need to hide all columns with all zero values in the rows. Thanks again!

 

[share_knowledge]

Here is the code i copied from Aki-Amur. Thanks!

Sub Macro2()



For Each r In Range("B1:AD1")

If r.Value = 0 Then

r.EntireColumn.Hidden = True

Else

If r.Value = "" Then

r.EntireColumn.Hidden = True

Else

r.EntireColumn.Hidden = False

End If

End If

Next



End Sub

 

[iliace]

Public Sub testSum()
  Dim rng As Excel.Range
  
  For Each rng In ActiveSheet.UsedRange.Columns
    If Application.WorksheetFunction.Sum(rng) = 0 Then
      rng.EntireColumn.Hidden = True
    End If
  Next rng
End Sub

Let's suppose you want to do this each time a worksheet change occurs. Further let's suppose that your worksheet changes as a result of certain macros, not just by manual entry. In your worksheet's code module, use this code:

Private Sub Worksheet_Change(ByVal Target As Range)
  Application.ScreenUpdating = False
  Dim rng As Excel.Range
  
  For Each rng In ActiveSheet.UsedRange.Columns
    If Application.WorksheetFunction.Sum(rng) = 0 Then
      rng.EntireColumn.Hidden = True
    Else
      rng.EntireColumn.Hidden = False
    End If
  Next rng
  Application.ScreenUpdating = True
End Sub

 

[mikerickson]

Dim oneColumn As Range
For Each oneColumn In ActiveSheet.UsedRange.Columns
    oneColumn.Hidden = (Application.CountIf(oneColumn, 0) = Application.Count(oneColumn))
Next oneColumn

You can use COUNTA depending on how you want text cells handled.

 

[iliace]

Good point... counting zeroes is the better way, since summing may yield zero even when some cells are not.

 

[mikerickson]

If the data permits, Sum is faster than CountIf. Expecially with 6K rows.

 

[Peter_SSs]

Mike

Using the same basic structure, any idea how Max/Min would go for speed?

<font face=Courier New><SPAN style="color:#00007F">Dim</SPAN> oneColumn <SPAN style="color:#00007F">As</SPAN> Range<br><br><SPAN style="color:#00007F">For</SPAN> <SPAN style="color:#00007F">Each</SPAN> oneColumn <SPAN style="color:#00007F">In</SPAN> ActiveSheet.UsedRange.Columns<br>    oneColumn.Hidden = (WorksheetFunction.Max(oneColumn) = 0) _<br>                        * (WorksheetFunction.Min(oneColumn) = 0)<br><SPAN style="color:#00007F">Next</SPAN> oneColumn</FONT>

 

[pklimchuk]

How can I apply this macro on all worksheets

Dim oneColumn As Range
For Each oneColumn In ActiveSheet.UsedRange.Columns
    oneColumn.Hidden = (Application.CountIf(oneColumn, 0) = Application.Count(oneColumn))
Next oneColumn

You can use COUNTA depending on how you want text cells handled.