Using VBA to hide columns based on value in specific cells

Question

I am trying to come up with the code that will hide columns for a material summary sheet if the total for that column is zero. (ie if that column is not used i want to hide it).  see the attached image as an example. 

i have a simple If code that works but my spreadsheet has 60 columns of data that would need to be checked and i wanted to see if there was a faster way than what i have:

Sub Button_HideColumns_Unused()

'

'Button_HideColumns_Unused

'Hides all the columns that are not used

'

If Range("I367").Value = 0 Then ActiveSheet.Range("I:I").EntireColumn.Hidden = True

If Range("j367").Value = 0 Then ActiveSheet.Range("j:j").EntireColumn.Hidden = True

If Range("k367").Value = 0 Then ActiveSheet.Range("k:k").EntireColumn.Hidden = True

If Range("l367").Value = 0 Then ActiveSheet.Range("l:l").EntireColumn.Hidden = True

End Sub

I was looking into using Case but i know very little about vba so any help would be great!

Answer 1

Sub Button_HideColumns_Unused()

    Dim lngR As Long

    Dim lngC As Long

    Cells.EntireColumn.Hidden = False

    lngR = Cells(Rows.Count, "I").End(xlUp).Row ' Use a column that has the formula at the bottom

    'Change the 1 to the first column that you want to have hidden B= 2, C= 3, etc.

    For lngC = 1 To ActiveSheet.UsedRange.Columns.Count

        If Cells(lngR, lngC).Value = 0 Then Columns(lngC).Hidden = True

    Next lngC

End Sub

Answer 2

I realized i mis-typed my question, my goal is to hide the columns when the total row shows 0. i have reworked the code slightly but now i am having a new problem. Because the spreadsheet has merged cells at the top, whent he code runs it ends up selecting everything below the merged cell rather than just the single column. I have been trying to figure out how to collect the specific column based on the selected cell but i havent had any success

in this example id like to hide columns f and h.

here is the code i have. the named range "totalrow" is cells c12:j12 in this case

and the issue im having when i run it, i need it to select the single column where the total = 0 and hide that column

Answer 3

With the code that I posted, changing 

    For lngC = 1 To ActiveSheet.UsedRange.Columns.Count

to

    For lngC = 3 To ActiveSheet.UsedRange.Columns.Count

so that A and B don't get processed.

BEFORE (Column F has 0 as a total):

AFTER (Column F is hidden):