Automatic VBA Macro: Hide Columns Based on Cell Value

[SvelteRose]

Hello all,

I am using Excel 2007 in Windows 7. My issue is that I need columns to automatically hide themselves without having to Alt+F8 and running the program. My data set is:

<table style="border-collapse: collapse; width: 336pt;" border="0" cellpadding="0" cellspacing="0" width="448"><col style="width: 48pt;" span="7" width="64"> <tbody><tr style="height: 12pt;" height="16"> <td class="xl69" style="height: 12pt; width: 48pt;" height="16" width="64">
</td> <td class="xl69" style="width: 48pt;" width="64">A</td> <td class="xl69" style="width: 48pt;" width="64">B</td> <td class="xl69" style="width: 48pt;" width="64">C</td> <td class="xl69" style="width: 48pt;" width="64">D</td> <td class="xl69" style="width: 48pt;" width="64">E</td> <td class="xl69" style="width: 48pt;" width="64">F</td> </tr> <tr style="height: 12pt;" height="16"> <td class="xl69" style="height: 12pt;" align="right" height="16">1</td> <td class="xl71" align="right">1981</td> <td class="xl71" style="border-left: medium none;" align="right">1982</td> <td class="xl71" style="border-left: medium none;" align="right">1983</td> <td class="xl71" style="border-left: medium none;" align="right">1984</td> <td class="xl71" style="border-left: medium none;" align="right">1985</td> <td class="xl71" style="border-left: medium none;" align="right">1986</td> </tr> <tr style="height: 12pt;" height="16"> <td class="xl69" style="height: 12pt;" align="right" height="16">2</td> <td class="xl70" style="border-top: medium none;" align="right">4</td> <td class="xl70" style="border-top: medium none; border-left: medium none;" align="right">2</td> <td class="xl70" style="border-top: medium none; border-left: medium none;" align="right">1</td> <td class="xl70" style="border-top: medium none; border-left: medium none;" align="right">3</td> <td class="xl70" style="border-top: medium none; border-left: medium none;" align="right">6</td> <td class="xl70" style="border-top: medium none; border-left: medium none;" align="right">3</td> </tr> <tr style="height: 12pt;" height="16"> <td class="xl69" style="height: 12pt;" align="right" height="16">3</td> <td class="xl70" style="border-top: medium none;" align="right">5</td> <td class="xl70" style="border-top: medium none; border-left: medium none;" align="right">3</td> <td class="xl70" style="border-top: medium none; border-left: medium none;" align="right">2</td> <td class="xl70" style="border-top: medium none; border-left: medium none;" align="right">7</td> <td class="xl70" style="border-top: medium none; border-left: medium none;" align="right">9</td> <td class="xl70" style="border-top: medium none; border-left: medium none;" align="right">4</td> </tr> <tr style="height: 12pt;" height="16"> <td class="xl69" style="height: 12pt;" align="right" height="16">4</td> <td class="xl70" style="border-top: medium none;" align="right">6</td> <td class="xl70" style="border-top: medium none; border-left: medium none;" align="right">5</td> <td class="xl70" style="border-top: medium none; border-left: medium none;" align="right">2</td> <td class="xl70" style="border-top: medium none; border-left: medium none;" align="right">5</td> <td class="xl70" style="border-top: medium none; border-left: medium none;" align="right">6</td> <td class="xl70" style="border-top: medium none; border-left: medium none;" align="right">6</td> </tr> </tbody></table>
If the value in Row 1 equals 0, I want that entire column to automatically hide itself. So if D1 = 0, then Column D would automatically hide itself. Any suggestions?

 

[Domenic]

Try the following macro, which needs to be pasted into the the worksheet module...

Option Explicit

Private Sub Worksheet_Change(ByVal Target As Range)
    Dim Cell As Range
    Set Target = Intersect(Target, Target.Parent.Rows(1))
    If Not Target Is Nothing Then
        For Each Cell In Target
            Cell.EntireColumn.Hidden = (Cell.Value = 0)
        Next Cell
    End If
End Sub

 

[SvelteRose]

Do I need to replace the code with anything?

 

[Domenic]

No... However, make sure that the code is pasted into the sheet module (right-click sheet tab, and select 'View Code'), not a standard module.

 

[SvelteRose]

Alright, for some reason, the macro isn't working. I shall try to explain better... This is the spreadsheet I am working with:

<table style="border-collapse: collapse; width: 384pt;" border="0" cellpadding="0" cellspacing="0" width="512"><col style="width: 48pt;" span="8" width="64"> <tbody><tr style="height: 12pt;" height="16"> <td class="xl67" style="height: 12pt; width: 48pt;" height="16" width="64">
</td> <td class="xl69" style="width: 48pt;" width="64">A</td> <td class="xl69" style="width: 48pt;" width="64">B</td> <td class="xl69" style="width: 48pt;" width="64">C</td> <td class="xl69" style="width: 48pt;" width="64">D</td> <td class="xl69" style="width: 48pt;" width="64">E</td> <td class="xl69" style="width: 48pt;" width="64">F</td> <td class="xl69" style="width: 48pt;" width="64">G</td> </tr> <tr style="height: 12pt;" height="16"> <td class="xl67" style="height: 12pt;" align="right" height="16">1</td> <td class="xl68">Act/Comp:</td> <td class="xl67" colspan="2" style="">Actual (1981-1985)</td> <td class="xl67">
</td> <td class="xl67">
</td> <td class="xl67">
</td> <td class="xl67">
</td> </tr> <tr style="height: 12pt;" height="16"> <td class="xl67" style="height: 12pt;" align="right" height="16">2</td> <td class="xl68">Acct Type:</td> <td class="xl67">EBITDA</td> <td class="xl67">
</td> <td class="xl67">
</td> <td class="xl67">
</td> <td class="xl67">
</td> <td class="xl67">
</td> </tr> <tr style="height: 12pt;" height="16"> <td class="xl67" style="height: 12pt;" align="right" height="16">3</td> <td class="xl67">
</td> <td class="xl67">
</td> <td class="xl67">
</td> <td class="xl67">
</td> <td class="xl67">
</td> <td class="xl67">
</td> <td class="xl67">
</td> </tr> <tr style="height: 12pt;" height="16"> <td class="xl67" style="height: 12pt;" align="right" height="16">4</td> <td class="xl68">Ticker</td> <td class="xl68" align="right">1981</td> <td class="xl68" align="right">1982</td> <td class="xl68" align="right">1983</td> <td class="xl68" align="right">1984</td> <td class="xl68" align="right">0</td> <td class="xl68" align="right">0</td> </tr> <tr style="height: 12pt;" height="16"> <td class="xl67" style="height: 12pt;" align="right" height="16">5</td> <td class="xl67">AAPL</td> <td class="xl67" align="right">3</td> <td class="xl67" align="right">5</td> <td class="xl67" align="right">2</td> <td class="xl67" align="right">3</td> <td class="xl67" align="center">#N/A</td> <td class="xl67" align="center">#N/A</td> </tr> <tr style="height: 12pt;" height="16"> <td class="xl67" style="height: 12pt;" align="right" height="16">6</td> <td class="xl67">APP.OS</td> <td class="xl67" align="right">5</td> <td class="xl67" align="right">7</td> <td class="xl67" align="right">4</td> <td class="xl67" align="right">4</td> <td class="xl67" align="center">#N/A</td> <td class="xl67" align="center">#N/A</td> </tr> </tbody></table>
So basically, if the values in Row 4 say 0, I want them to hide the columns. Here, it would be F and G hidden. I would like the code to be automatic without having to run the macro. I C&P it into the worksheets module but nothing happened even after closing and reopening the excel file. The reason I asked if the code needed to reference anything because I was wondering how it separated Row 4 from all the other empty cells. If F4 said 0, I'd want the entire column of F to be hidden, not with F3 said 0.

Not sure what I'm doing wrong, any suggestions?

 

[SvelteRose]

I forgot to mention that the reason those values would be zero is a formula pulling that data and pulling the 0. I wasn't sure if this affects the coding any but just in case.

 

[SvelteRose]

I tried the code again and it does nothing. Do I have to do anything else except C&P the code into the sheet module, then click on the red x?

 

[Domenic]

The macro I offered is triggered only when there's a change in a cell within the worksheet. However, from what I see by your last sample data and by the new thread that you've started, the macro will need to change. If you answer the question posed by Lenze, a solution can be offered. To make things simple, let's continue in your new thread...

http://www.mrexcel.com/forum/showthread.php?p=2289878#post2289878