Conditionally Hide a Column or Row

Question

I would like to be able to hide a column based on the result of a value within the column (the same for a row). Basically if a column has a total value where the total = 0 then I want to hide the column. Example below, Column A is totaled at 15, Column B is totaled at 0, therefore I want to hide ColumnB: Any advice would be great.

ColumnA  ColumnB

10               0

5                 0

15               0

---

Leo

Answer 1

if the column totals are on row 100 for example and you want to check colums A:Z you could do this

Sub HideColumn()

    Dim cell As Range

    For Each cell In Range("A100:Z100")

      If cell = 0 Then

           cell.EntireColumn.Hidden = True

       End If

   Next cell

End Sub

Note as written this does not unhide columns whose value on row 100 is not >0.  Also this macro requires that you run it manually, you could make it run automatically by attaching it to a Change event.

This macro is for hiding columns, some change would give you ones to hide rows. 

---

If this answer solves your problem, please check Mark as Answered. If this answer helps, please click the Vote as Helpful button. Cheers, Shane Devenshire

Answer 2

Hi,

I assume your totals are in row 3 change it to fit your needs, use this code

Sub Macro2()

'

' Macro2 Macro

'

For a = 1 To 255

If Range("a3").Offset(0, a).Value <= 0 Then

Range("a3").Offset(0, a).EntireColumn.Hidden = True

End If

Next a

'

End Sub

if your totals are in row 100 just change a3 for a100

For a = 1 to 255 are the columns to be considered change it as well to fit your needs

P.S. If any post answers your question, please mark it as the Answer (so it won't keep showing as an open item.)

Answer 3

Leo,

You've marked this as answered and Shane's code will certainly do what you asked.  However I was just curious - why are you looking to hide the column?  Or more to the point must you hide the column?  Do you have other columns to the right that you want to snug up?  If so, the yes, hiding the column is just what the doctor ordered.  But if you're trying to make things just look a bit cleaner, i.e. you prefer an empty column in lieu of a column full of zeros, then perhaps a bit of custom formatting would do the trick.

Likewise, as Shane has mentioned, if truly hiding the code is the right answer, then you might want to tie this to an event.  Shane mentions theChange event.  To go just a bit further, please be aware that should column B [or whatever column your code needs to review] contains formulae with off-sheet precedents, you would also want to have the worksheet'sCalculate event call the column-peek-a-boo code as well.

Regards,

Greg