Hide Columns (F:MC) with Zero value

[rtehlan]

Hi,

Can anyone help me with this ? I am currently using a macro that would hide all the column which sums to 0 including (A:E) which are text but i only want it to work from Column (F:MC). Please note that i have row headers on row 9 throughout these columns. Any help would be greatly appreciated

Sub HideZeroColumn()


Dim anyWS As Worksheet
Dim anyTotalRow As Range
Dim anyCell As Range
Set anyWS = ActiveSheet
Set anyTotalRow = anyWS.Range(Cells(ActiveCell.Row, 2).Address, _
Cells(ActiveCell.Row, _
anyWS.Cells(ActiveCell.Row, Columns.Count). _
End(xlToLeft).Column))


Application.ScreenUpdating = False


anyTotalRow.Columns.EntireColumn.Hidden = False
For Each anyCell In anyTotalRow
If IsEmpty(anyCell) Or anyCell = 0 Then
anyCell.EntireColumn.Hidden = True
End If
Next
Set anyTotalRow = Nothing
Set anyWS = Nothing
End Sub

 

[Joe4]

I am not entirely clear which columns you want to apply this to. I wrote it for columns F:MC, but that can be changed easily.

Maybe something like this:

Sub HideZeroColumn()

    Dim myCol As Long
    Dim colAdd As String
    
    Application.ScreenUpdating = False

'   Loop through all columns from F to MC
    For myCol = 6 To 341
        colAdd = Replace(Columns(myCol).Address, "$", "")
'       Hide column if sum is zero
        If WorksheetFunction.Sum(Range(colAdd)) = 0 Then
            Columns(myCol).Hidden = True
        End If
    Next myCol
    
    Application.ScreenUpdating = True
    
End Sub

 

[rtehlan]

Works perfectly, Thank you so much !!!

 

[Joe4]

You are welcome!